MySQL Forums :: Performance :: Unused index, slow query


Advanced Search

Unused index, slow query
Posted by: Matthieu Bonne ()
Date: May 20, 2008 06:48AM

Hello,

First, please excuse me if my english is average, it isn't my native language :-)

I have a rather big query which runs between 20 and 50 times a day and takes between 1 and 5 secondes to run. Here is the original version:

SELECT DISTINCT s2.nom, s2.x, s2.y, s2.vaisseaux, s2.dev, s2.dev_max, IF (j.capitale = s2.nom, 1,0) as cap, j.couleur
FROM systemes s1, systemes s2, joueurs j
WHERE s1.pseudo = "A PHP-given parameter"
AND s1.nom <> s2.nom
AND s2.pseudo=j.login
AND (pow((s1.x - s2.x), 2) + pow((s1.y - s2.y), 2)) <= 'A PHP-given number'

I ran EXPLAIN on it and it tells me s1 and j use a "ref" type index, but s2 is "ALL" so the full table is parsed; i guess the performance leak comes from here.

Since i do have an index on "pseudo" field (and s2's "possible-keys" displays "pseudo", so my index should work !) i tried to force its use with "USE INDEX (pseudo)", then even "FORCE INDEX (pseudo)"... Nothing changed, at all...

I then thought my SQL query should be rewritten; i transformed my WHERE joins into proper inner joins, like this:

SELECT DISTINCT s2.nom, s2.x, s2.y, s2.vaisseaux, s2.dev, s2.dev_max, IF (j.capitale = s2.nom, 1,0) as cap, j.couleur
FROM systemes s1
LEFT JOIN
(systemes s2 JOIN joueurs j ON s2.pseudo=j.login)
ON s1.nom <> s2.nom
WHERE s1.pseudo = "A PHP-given parameter"
AND (pow((s1.x - s2.x), 2) + pow((s1.y - s2.y), 2)) <= pow(50, 2)

But the EXPLAIN result is still the same, and the query didn't run faster.

I desperately boosted some settings in my my.cnf (read buffer size, etc) and notice an average 5% boost of performance. Uh...

So... I really don't know what to do now. Rewrite the SQL in another fashion, maybe using a syntax i don't know well enough ? Tweak my MySQL server ? Create another index ?

FYI, s2.pseudo and j.login are both "varchar(20)", and i have MySQL v5.0.21-log. Here is the full result from EXPLAIN (similar in both queries):
1 SIMPLE s1 ref pseudo pseudo 23 const 488 Using where; Using temporary
1 SIMPLE s2 ALL pseudo NULL NULL NULL 900 Using where
1 SIMPLE j ref PRIMARY PRIMARY 22 ano4hof.s2.pseudo 1 Using where

I hope someone here can help me. Thank you very much for reading my post and taking a little time to share your experience with me :-)

Matthieu



Edited 2 time(s). Last edit at 05/20/2008 06:56AM by Matthieu Bonne.

Options: ReplyQuote


Subject Views Written By Posted
Unused index, slow query 3238 Matthieu Bonne 05/20/2008 06:48AM
Re: Unused index, slow query 1433 Lars Johansson 05/21/2008 09:08PM
Re: Unused index, slow query 1267 Rick James 05/28/2008 12:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.