[MySQL] Gebruik van temporary table voorkomen

[Zr40]

Ik ben een query aan het schrijven om het aantal aanwezige entities per tijdspanne weer te geven. Ik heb echter het probleem dat volgens EXPLAIN een temporary table gebruikt wordt. Dit kan niet, aangezien de tabel in kwestie ongeveer 200K rows heeft, en uiteindelijk zo'n 10M rows krijgt.

De query zoals ik hem nu heb: (De waarde 117 is niet hardcoded, maar eerder opgevraagd.)

SQL:

SELECT utrancell.fld_spawntime, COUNT(utrancell.fld_entityid)
FROM tbl_trenddata_ranos_utrancell_qtr utrancell
INNER JOIN tbl_entity entity ON entity.fld_id = utrancell.fld_entityid
WHERE entity.fld_parentid = 117
GROUP BY utrancell.fld_spawntime DESC

De output van EXPLAIN:

code:

+----+-------------+-----------+------+----------------------+--------------+---------+----------------------+------+---------------------------------+
| id | select_type | table     | type | possible_keys        | key          | key_len | ref                  | rows | Extra                           |
+----+-------------+-----------+------+----------------------+--------------+---------+----------------------+------+---------------------------------+
|  1 | SIMPLE      | entity    | ref  | PRIMARY,fld_ParentID | fld_ParentID | 4       | const                |  229 | Using temporary; Using filesort |
|  1 | SIMPLE      | utrancell | ref  | fld_EntityID         | fld_EntityID | 4       | kronos.entity.fld_ID |   61 |                                 |
+----+-------------+-----------+------+----------------------+--------------+---------+----------------------+------+---------------------------------+

Het vreemde is, dat wanneer ik de WHERE weghaal, er geen temporary table gebruikt wordt.

Ik heb geen flauw idee wat ik over het hoofd zie. De query doet het op zich gewoon goed, maar is veel te langzaam door het gebruik van een temporary table. Aan verkeerde indexen lijkt het ook niet te liggen.

[ Voor 3% gewijzigd door Zr40 op 28-06-2006 14:05 . Reden: Typofix ]

[The Fox NL]

En wat als je de code verandert in dit:

SQL:

SELECT utrancell.fld_spawntime, COUNT(utrancell.fld_entityid)
FROM tbl_trenddata_ranos_utrancell_qtr utrancell
INNER JOIN tbl_entity entity ON (entity.fld_id = utrancell.fld_entityid AND
entity.fld_parentid = 117)
GROUP BY utrancell.fld_spawntime DESC

Wat doet ie dan?

[Zr40]

Dan gebeurt precies hetzelfde. Mijn ervaring tot nu toe is dat het MySQL niets uit maakt of je de clauses achter WHERE of achter INNER JOIN [..] ON zet. Voor de leesbaarheid is dat echter niet bevorderlijk

[ Voor 2% gewijzigd door Zr40 op 28-06-2006 14:06 . Reden: INNER toegevoegd. Bij andere JOINs maakt het wel verschil. ]

[Vesta]

Je gebruikt de kolom entity.fld_id in een JOIN en de kolom entity.fld_parentid in de WHERE clausule. Op beide kolommen heb je zo te zien een index (PRIMARY en fld_ParentID). Voor zover ik weet, gebruikt MySQL slechts 1 index per tabel. Dat verklaart dan ook waarom er geen temporary table wordt gebruikt als je de WHERE clausule weghaalt: er is dan maar 1 index nodig ipv 2.

Probeer eens een samengestelde index op de kolommen entity.fld_id en entity.fld_parentid, mogelijk wordt deze index dan gebruikt.

[_js_]

MySQL kan niet twee verschillende indices gebruiken voor 1 tabel in 1 query, dus je moet één samengestelde index maken specifiek voor deze query als je het gebruik van de temporary table wilt omzeilen.

[Zr40]

Ik heb de index op fld_ParentID uitgebreid zodat deze ook fld_ID omvat. Dit helpt.
Weer iets om in het achterhoofd te houden. Bedankt!