First off, please excuse me if this is the wrong list to post this sort of question to (and please direct me to the appropriate list)
My problem: I have a database of approx. 170,000 records and 1.2 million keywords. Most of my searches happen fairly quickly using the format below However, the "group by" is causing a temporary table when I "explain" my statement, which significantly lengthens query time. If I eliminate the group by, queries are substantially sped up and a temp table is not being generated. Only problem with not using "group by" is that sometimes the same ID is duplicated in the results due to some of the keywords being very similar (i.e. "Richard", "Rich", "Rick") for the same ID. Can anyone help with a different Select strategy or something else to eliminate the temp table being generated for this query. --------------------------------------------- Tables: main - has unique ID (primary) column and main data columns kw_table - has ID column and a keyword column Indexes are created on the ID columns and the keyword column. Query: SELECT main.* FROM kw_table, kw_table AS kw_table1, main WHERE kw_table LIKE 'first word%' AND kw_table1 LIKE '2nd word%' AND main.id = kw_table.id AND kw_table1.id = kw_table.id GROUP BY main.id Limit 0,11 -------------------------------------------- Thanks Gabe --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
