php - This query/database is not working well together -


here's query:

select     *,    count(*) `numauth`  `favorites` `f1`  inner join `story` `s1` on `f1`.`story_id` = `s1`.`story_id`  `f1`.`story_id` != '".addslashes($_request['storyid'])."'     , `f1`.`story_id` != '".addslashes($_request['storyid2'])."'     , exists (                select 1 `favorites` `f2`                  `story_id` = '".addslashes($_request['storyid'])."'                  , `f2`.`auth_id` = `f1`.`auth_id`)                  , exists (                             select 1 `favorites` `f3`                              `story_id` = '".addslashes($_request['storyid2'])."'                              , `f3`.`auth_id` = `f1`.`auth_id`)                              , not exists (                                             select 1 `favorites` `f4`                                              `story_id` =                                              '".addslashes($_request['exclude'])."'                                                                                                                                               `f4`.`auth_id` = `f1`.`auth_id`)  group `f1`.`story_id`  order `numauth` desc, `story_words` desc

and here's description of tables...

create table if not exists `favorites` (   `fav_id` int(10) unsigned not null auto_increment,   `auth_id` int(10) unsigned not null,   `story_id` int(10) unsigned not null,   primary key (`fav_id`),   unique key `auth_id_2` (`auth_id`,`story_id`),   key `auth_id` (`auth_id`),   key `story_id` (`story_id`),   key `fav_id` (`fav_id`,`auth_id`,`story_id`) ) engine=myisam  default charset=utf8 auto_increment=1577985 ;  create table if not exists `story` (   `story_id` int(10) unsigned not null,   `story_title` varchar(255) not null,   `story_desc` text not null,   `story_authid` int(8) unsigned not null,   `story_authname` varchar(255) not null,   `story_fandom` varchar(255) not null,   `story_genre1` tinyint(2) unsigned not null,   `story_genre2` tinyint(2) unsigned not null,   `story_created` int(10) unsigned not null,   `story_updated` int(10) unsigned not null,   `story_reviews` smallint(5) unsigned not null,   `story_chapters` smallint(3) unsigned not null,   `story_rating` tinyint(2) unsigned not null,   `story_words` mediumint(7) unsigned not null,   `story_chars` varchar(255) not null,   unique key `story_id` (`story_id`),   key `story_authid` (`story_authid`),   key `story_fandom` (`story_fandom`),   key `story_authid_2` (`story_authid`,`story_fandom`),   key `story_id_2` (`story_id`,`story_authid`),   key `story_id_3` (`story_id`,`story_words`),   key `story_id_4` (`story_id`,`story_fandom`,`story_words`),   key `story_id_5` (`story_id`,`story_reviews`,`story_words`),   key `story_words` (`story_words`),   key `story_reviews` (`story_reviews`) ) engine=myisam default charset=utf8;

now i've done fair bit of optimizing query down this. i'm running on dedicated server query still taking 5-7 seconds, unacceptable. we're looking @ 800,000 records on favorites , 400,000 records on stories, , i'm lost @ point on next improvements.

it seems bit daunting, if can point me in right direction i'll happy.

explain sample inputs:

id  select_type     table   type    possible_keys   key     key_len     ref     rows     1   primary s1  story_id,story_id_2,story_id_3,story_id_4,story_id...   null    null    null    129429  using where; using temporary; using filesort 1   primary f1  ref story_id    story_id    4   fanfic_jordanl_ffrecs.s1.story_id   2   using 4   dependent subquery  f4  eq_ref  auth_id_2,auth_id,story_id  auth_id_2   8   fanfic_jordanl_ffrecs.f1.auth_id,const  1   using index 3   dependent subquery  f3  eq_ref  auth_id_2,auth_id,story_id  auth_id_2   8   fanfic_jordanl_ffrecs.f1.auth_id,const  1   using index 2   dependent subquery  f2  eq_ref  auth_id_2,auth_id,story_id  auth_id_2   8   fanfic_jordanl_ffrecs.f1.auth_id,const  1   using index

try this:

select  f1.*, s1.*, count(*) `numauth`  `favorites` `f1`  inner join `story` `s1` on `f1`.`story_id` = `s1`.`story_id`  inner join (         select auth_id         favorites         story_id in ('".addslashes($_request['storyid'])."', '".addslashes($_request['storyid2'])."', '".addslashes($_request['exclude'])."')         group auth_id         having count(if(story_id = '".addslashes($_request['exclude'])."', 1, null)) = 0 , count(*) = 2          ) fv on f1.auth_id = fv.auth_id `f1`.`story_id` != '".addslashes($_request['storyid'])."'     , `f1`.`story_id` != '".addslashes($_request['storyid2'])."'  group `f1`.`story_id`  order `numauth` desc, `story_words` desc

since selecting * not grouping auth_id, trying do?

--- update since not need fav info stories, query should perform better:

select s.*, fv.cnt story s     join (         select fv.story_id, count(*) cnt         favorites fv             join (                 select auth_id                 favorites                 story_id in ('".addslashes($_request['storyid'])."', '".addslashes($_request['storyid2'])."', '".addslashes($_request['exclude'])."')                 group auth_id                 having count(if(story_id = '".addslashes($_request['exclude'])."', 1, null)) = 0 , count(*) = 2              ) ufv on fv.auth_id = ufv.auth_id         story_id != '".addslashes($_request['storyid'])."' , story_id != '".addslashes($_request['storyid2'])."'          group fv.story_id         order count(*) desc         limit 25     ) fv on s.story_id = fv.story_id order fv.cnt desc, `story_words` desc

Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc...
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use...
Read more