Can anyone help speed this up??
Posted by:
Bruce D
Date: May 10, 2005 07:44AM
Here's the breakdown:
MySQL 4.0.12
Table: Assignment (indexes are created on proper fields) 419,234 records
Table: Finders (indexes are created on proper fields) 5,345,657 records
My user wants to be able to do a wild card search on lastname in the finders
table. So, I wrote the following query.
SELECT Finders.Lastname, Assignment.currentcard
FROM Finders JOIN Assignment on Finders.assignmentid=Assignment.assignmentid
WHERE Assignment.jobnumber='50720'
AND Finders.lastname like 'DU%'
This query takes about 35 seconds to return the 33,000 records. Not
acceptable. From my understanding, I'm pretty much stuck since I'm using
the 'LIKE' and wild card. True?
I tried using "AND left(Finders.lastname,2) = 'DU'...but that didn't seem to
speed anything up.
I tried using "AND substring(Finders.lastname,1,2) = 'DU' ... but that
didn't seem to speed up either.
I tried
SELECT Finders.Lastname, Assignment.currentcard
FROM Finders, Assignment
WHERE Finders.assignmentid=Assignment.assignmentid
AND Assignment.jobnumber='50720'
AND Finders.lastname like 'DU%'
but that didn't work either.
So, I tried
create temporary table JOBS select assignmentid, currentcard, jobnumber from
Assignment where jobnumber = '50720';
SELECT JOBS.currentcard, JOBS.assignmentid, Finders.firstname
FROM JOBS join Finders on Finders.assignmentid = JOBS.assignmentid
WHERE Finders.lastname like 'MI%'
but I'm not getting very good times there either.
I'm thinking version 5.0 would help, but that's not a possibilty.
Does anyone have any ideas? I'm in dire need of some help.
-Bruce