[RESOLVED] Query is causing mysqld.exe CPU to spike to 99% but eventually works

Square1

Hi all:

Switching my site from ASP Classic and MSSQL to PHP and MySQL. Something weird is going on with the following query. It is causing my mysqld.exe to spike to 99% CPU and is killing my site. Its results are about 315 records. When I set a LIMIT to 10 results, I still get the spike but after about 30 seconds the 10 results do appear. So I know the code "works" as I get results but what is causing this this spike in my mysqld.exe CPU usage?????

if ($result = mysqli_query($link, "
			SELECT
			ae.player,
			sports.sport,
			ae.promoter,
			promoters.promoterFirstName,
			promoters.promoterLastName,
			promoters.promoterPhone,
			promoters.promoterEmail,
			ae.exclusive,
			ae.agreementEnd,
			ae.repSince,
			ae.reference
			FROM
			sports
			INNER JOIN
			((ae INNER JOIN promoters ON ae.prID = promoters.prID)
			INNER JOIN
			players ON ae.plID = players.plID) ON sports.sportCode = players.sportCode
"))

dalecosp

Just offhand, I'd guess it's the query 😉

Square1

dalecosp;10988651 wrote:
Just offhand, I'd guess it's the query 😉

Even though it does eventually bring up a result set? Is there a better way to write this?

dalecosp

I'm not guru enough in SQL to know, unfortunately. I'd certainly hope that there is ... it looks pretty darn complex to me.

I do know that on our site, mysqld is what's killing us, too --- so I go 'round looking for stuff like this, analyze just exactly what needs done, try to find an alternate plan, etc. We're making a little progress. As I said, unfortunately, I don't usually just rewrite a query ... I rewrite the surrounding code to do the same job with simpler queries, or some other way.

NogDog

Start by making sure you have a DB index on each field that is used in the ON clause of your JOINs as well as within any WHERE or SORT clause. You can also mess around with EXPLAIN to get info from MySQL about where bottlenecks may be in the query.

Square1

NogDog;10988659 wrote:
Start by making sure you have a DB index on each field that is used in the ON clause of your JOINs as well as within any WHERE or SORT clause. You can also mess around with EXPLAIN to get info from MySQL about where bottlenecks may be in the query.

Thanks again NoDog. I thought I was going to be in a heap of trouble. I cleaned up the indexes and it is 98% better. Much appreciated!!!!!

naomi105813

Is there a easy way to write this?Another?

naomi105813

Is there a easy way to write this?Thanx...

naomi105813

naomi105813;10988828 wrote:
Is there a easy way to write this?Thanx...

naomi105813;10988827 wrote:
Is there a easy way to write this?Another?