I'm having tremendous problems with a query and I was hoping someone could point out where (if) I'm going wrong.
Using MySQL 4.0.15 on a LINUX box. Basically I have a database that lists a large number of web links cataloged by subject. The first table (records) contains all the link information (link id, prelink text, link text, postlink test, and url) The second table (record_cats) contains the subjects for each link (link id, mwsubject). Browsing by subject seems to work ok (it's a little slow sometimes) with the following select query SELECT DISTINCT r.prelink, r.link, r.postlink, r.url FROM records AS r JOIN record_cats AS rc ON r.lid = rc.lid WHERE rc.mwsubject LIKE 'foo' ORDER BY link LIMIT 0,30; 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? Thanks in advance for any insight. Scott
