Need help speeding up query...if possible...

[Bruce D]

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

[Matt]

Bruce D wrote:

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

How about this:

SELECT Finders.Lastname, Assignment.currentcard
FROM Finders JOIN Assignment on Finders.assignmentid=Assignment.assignmentid AND Assignment.jobnumber='50720'
WHERE Finders.lastname like 'DU%'

[Bill Karwin]

Bruce D wrote:

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.

Please post the actual DDL you used to create these tables and their
indexes. In other words, don't make readers guess what you considered
the proper fields to index, what table storage type you're using, how
long the string fields are, etc.

Also please post the results of running your query with EXPLAIN.

Regards,
Bill K.

[Bruce D]

Sorry...I lost formatting...

Here are the indexes.
Finders:
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 5659954 Drop Edit FinderID
FinderNumber INDEX 55 Drop Edit FinderNumber
StreetNumber INDEX 38242 Drop Edit StreetNumber
StreetName INDEX 82028 Drop Edit StreetName
Zip INDEX 1510 Drop Edit Zip
Phone INDEX 2829977 Drop Edit Phone
AssignmentID INDEX 435381 Drop Edit AssignmentID
Name INDEX 2829977 Drop Edit LastName, FirstName

Assignment:
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 419649 Drop Edit AssignmentID
KitStatusID INDEX 1 Drop Edit KitStatusID
AssignCode INDEX 419649 Drop Edit AssignCode
AssignZip INDEX 1509 Drop Edit AssignZip
AssignStreet INDEX 83929 Drop Edit AssignStreet
Volunteer_FinderID INDEX 419649 Drop Edit Volunteer_FinderID
BranchID INDEX 1 Drop Edit BranchID
JobNumber INDEX 1 Drop Edit JobNumber
CurrentCard INDEX 1 Drop Edit CurrentCard

Here's the explain.

EXPLAIN SELECT Finders.Lastname, Assignment.currentcard
FROM Finders
JOIN Assignment ON Finders.assignmentid = Assignment.assignmentid
WHERE Assignment.jobnumber = '50720' AND Finders.lastname
LIKE 'DU%'

Finders range AssignmentID,Name Name 15 NULL 32284 Using where
Assignment eq_ref PRIMARY,JobNumber PRIMARY 4 Finders.AssignmentID 1 Using
where

I'm using MyISAM tables.
TIA,
-bruce