Unused index, slow query
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.