After looking over your results, I would keep the dir1 index at least on the first and last table.
But since this data is read only, why not reformulate the data for the queries you are going to make? This is the opposite of normalizing, and will require more disk space, and is not flexible, but it will be fast. Of course, it depends on what you are doing and how many types of queries you have. This 'normalize by queries' or what I refer to as 'selective denormalization' likely won't appear in any books. It is a technique that I was taught from DBA's that have been doing this sort of stuff for Fortune100 companies for decades. I use it only rarely, and only where the data is needed in realtime where the structure of the data and its quantity would not normally return results so quickly. It would likely work in your case also. All the best, --steve- PS: Get a big fast SCSI RAID 10 array of 15K drives (stripe the mirrors). Actually, since this data is readonly and copies are stored elsewhere, you could get by with RAID 1. I'm guessing you are being held back by the disk, or your memory buffers. Oh, and just for fun: # order the files ALTER TABLE cg ORDER BY dir1; ALTER TABLE q ORDER BY dir1; ALTER TABLE cit ORDER BY dir1; ALTER TABLE sref ORDER BY dir1; # 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; # order the files ALTER TABLE cg ORDER BY dir2; ALTER TABLE q ORDER BY dir2; ALTER TABLE cit ORDER BY dir2; ALTER TABLE sref ORDER BY dir2; # 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; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
