Problem query uses 99% of CPU

[mgrove]

Hi am trying to run a simple query on a mysql database and it seems to bring my database server to its knees. I can not figure out why. I need help!

Below is my problem query:

SELECT scs.classNumber, l.locationID, c.courseID
FROM sunclassschedule scs, location l, course c
WHERE scs.locationCode = l.locationCode
AND scs.courseCode = c.courseCode

The relationship between scs and l is one to one. The same goes for scs and c. The fields that I am using to join these tables are text fields that are unique but not the primary key on the tables. scs have about 2600 records in it. l has 240 records and c has 460 records. This query should return 2600 records, which it does, but it takes about ten minutes to process at 99% cpu utilization.

When I run this query the mysql process starts out at a very low cpu utilization and slowly grows until it reaches 99%.

Does anyone have any idea what is going on with this query?

Thanks,
Michael Grove

 

[cdlvj]

Not necessarily the primary key, but you need scs key of locationcode,coursecode; l location code, and c coursecode.

Use explain to see what keys are being used. I believe there is an option on select to use particular keys.

 

[BNPMike]

With those low cardinalities it shouldn't matter what you do - it should go like a rocket. You're just going to have to change things around to see what's upsetting it - trial and error.

 

[vanekl]

Are you running on a Commodore64? :>
What is your set-up like?
That is, what OS?
How much RAM?
Have you tuned any of the MySQL variables recently?
Does this only happen on this one query? Are all other
queries fast?

Do you have any {TINY|MEDIUM|LONG}BLOB or TEXT fields in these
tables? If so, I have seen MySQL dramatically increase its speed
when they were either changed to VARCHAR or moved into a
separate table.