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