> All the indexes were single indexes, partly because I haven't > yet made the effort to understand composite index. I guess it's > time ;-).
Oh. There are better places to start than this list. ;) The manual can be a great starting place, and several people on this list have written books about MySQL which are great for getting started. You can look at Amazon.com, etc. > mysql> EXPLAIN SELECT 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; You do a range on the fist and last table in the chain. :( It is best to normalize your table structure. I should have noticed that upfront. The 'simple, well-indexed query' through me off. Normalizing is a great thing to learn, and probably the first thing to understand after how to do a SELECT and composite indexes. > What does this mean for regular searching? In most cases, there will be > some criteria entered that need to be searched on, and the id fields > will also be needed for the joins. For example, in the database, one > might want to search based on cg.exp (fulltext), sref.rdr, sref.cd > (the date field), sref.kbd, cit.w, and various other ones I've edited > out of this display to save space, and often a combination of several > of these at once. How should I set up indexes for the potential > searches that might be executed? > (I should mention that this is a read-only database; it's built from > a parsed SGML file and is never added to directly, if that's an > issue.) Well, I would rewrite the table design. :) It looks like it is taking its structure from the SGML format. Personally, I'd use that as an intermediate format in order to populate another set of tables that would be in normal form. Again, a book will help here. Sorry I can't be of more help. At least we went from 1 m 15 sec to 4.05 sec. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
