İşte bir sorgu:

SELECT 
   *,
   COUNT(*) as `numauth` 
FROM `favorites` as `f1` 
INNER JOIN `story` as `s1` ON `f1`.`story_id` = `s1`.`story_id` 
WHERE `f1`.`story_id` != '".addslashes($_REQUEST['storyid'])."' 
   AND `f1`.`story_id` != '".addslashes($_REQUEST['storyid2'])."' 
   AND EXISTS (
               SELECT 1 FROM `favorites` as `f2` 
                WHERE `story_id` = '".addslashes($_REQUEST['storyid'])."' 
                AND `f2`.`auth_id` = `f1`.`auth_id`) 
                AND EXISTS (
                            SELECT 1 FROM `favorites` as `f3` 
                            WHERE `story_id` = '".addslashes($_REQUEST['storyid2'])."' 
                            AND `f3`.`auth_id` = `f1`.`auth_id`) 
                            AND NOT EXISTS (
                                            SELECT 1 FROM `favorites` as `f4` 
                                            WHERE `story_id` = 
                                            '".addslashes($_REQUEST['exclude'])."'                                                                                                  
                                            `f4`.`auth_id` = `f1`.`auth_id`) 
GROUP BY `f1`.`story_id` 
ORDER BY `numauth` DESC, `story_words` DESC

Ve burada tabloları bir açıklaması var ...

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;

Şimdi bu aşağı sorgu almak için optimize adil düşeni yaptık. Ben adanmış bir sunucu üzerinde koşuyorum ama sorgu hala kabul edilemez olduğu, 5-7 saniye alıyor. Biz öyküleri üzerinde sık ve 400,000 kayıtları yaklaşık 800,000 kayıtlarına bakıyoruz, ve ben iyileştirmeler için bir sonraki bakmak için nerede bu noktada kaybettim.

Bu biraz zor görünüyor, bu yüzden birisi bana doğru yönde işaret edebilir bile ben mutlu olacağım.

Örneği girişli İZAH:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra 
1   PRIMARY s1  ALL 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 where
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