As I understand it, anything with LIKE '%foo', i.e. with the % at the front of the search string, has blown all indexing away. The only wan the database can perform this is by an exhaustive search though the whole table. If I undersyand correctly what you are doing, you probably want a FULLTEXT index in binary mode, using MATCH (r.prelink, r.link, r.postling, r.url) AGAINST 'foo*' See http://www.mysql.com/doc/en/Fulltext_Search.html . This might not woork with the URL, which is not split into words.
------------------------------------------ My real problem is when I try to build a search function with user input. In a search like this I need to have the text entered searched for across all the relavant rows that contain text, including the subject. My problem is this query runs well over 10 min. I have about 16 thousand records in the records table and about 93 thousand in the record_cats table and I'm using queries like: SELECT DISTINCT r.prelink, r.link, r.postlink, r.url FROM records AS r LEFT JOIN record_cats AS rc ON r.lid = rc.lid WHERE (r.prelink LIKE '%foo%' OR r.link LIKE '%foo%' OR r.postlink LIKE '%foo%' OR r.url LIKE '%foo%' OR rc.mwsubject LIKE '%foo%') ORDER BY link LIMIT 0,30 My question is, am I out of my gord with the search above? Is there a faster and better way to do this in MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
