Hmmm, just in case you can't change the table layout... Run this through MySQL. First I get rid of the other index I made, then add chained indexes so there is no need for data file lookup. Also, one direction of the query table join chain was not always using the indexes for the where. One direction is preferable (not knowing what the data is) since then we can use the index for the sort, but the other direction may have benefits that outweigh that, so that is why we used the composite index I last suggested.
Anyhow, just to be clear, lets force all composite indexes for this query in both direction and force the optimizer to use both directions and see what we get (and sorry if I misspell or something, since I don't have your DB to check against). Run this and send back the results: # Get rid of the index I added before ALTER TABLE cg DROP INDEX q_id_2; # Get a baseline for direction 1 SELECT STRAIGHT_JOIN cg.cw FROM cg,q,cit,sref WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM cg,q,cit,sref WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; # Get a baseline for direction 2 SELECT STRAIGHT_JOIN cg.cw FROM sref,cit,q,cg WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM sref,cit,q,cg WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; # add indexes ALTER TABLE cg ADD INDEX dir1(cw,q_id), ADD INDEX dir2(q_id,cw); ALTER TABLE q ADD INDEX dir1(id,cit_id), ADD INDEX dir2(cit_id,id); ALTER TABLE cit ADD INDEX dir1(id,sref_id), ADD INDEX dir2(sref_id,id); ALTER TABLE sref ADD INDEX dir1(id,cd), ADD INDEX dir2(cd,id); # Get a new result for direction 1 SELECT STRAIGHT_JOIN cg.cw FROM cg USE INDEX(dir1), q USE INDEX(dir1), cit USE INDEX(dir1), sref USE INDEX(dir1) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM cg USE INDEX(dir1), q USE INDEX(dir1), cit USE INDEX(dir1), sref USE INDEX(dir1) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; # Get a new result for direction 2 SELECT STRAIGHT_JOIN cg.cw FROM sref USE INDEX(dir2), cit USE INDEX(dir2), q USE INDEX(dir2), cg USE INDEX(dir2) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; EXPLAIN SELECT STRAIGHT_JOIN cg.cw FROM sref USE INDEX(dir2), cit USE INDEX(dir2), q USE INDEX(dir2), cg USE INDEX(dir2) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; # Send results back via email!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
