Scott, First, you don't mention indexes, - generally you need to make sure you've got the right indexes. Each table should have an index that contains every field in the where clause.
Second, searching for "%x%" is always slow as this search can't use indexes (search for "x%" can though). As it looks like you may be searching like "%foo%" on text fields. If this is the case, you should consider using full text search on these as it can use indexes and could therefore be quicker. Hope this helps, Andy > -----Original Message----- > From: Scott Turnbull [mailto:[EMAIL PROTECTED] > Sent: 22 October 2003 15:02 > To: [EMAIL PROTECTED] > Subject: Faster Query Method? > > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
