SQL

SQL in indeksi (2.del)

nedelja, 3. februar 2008 dan, ko je umrla glasba (1959)

Dva meseca nazaj sem že pisal o SQL in indeksih, nekaj na kratko in enostavno o tem, kaj sploh so, kako jih kreiramo in kako jih uporabljamo. Seveda se uporaba indeksov ne konča tam, kjer smo prejšnjič ostali, pa nadaljujmo.

Indeks nam lahko pomaga tudi pri hitrejšem sortiranju rezultatov. Operacija sortiranja je načeloma časovno požrešna operacija, pa bi bilo mogoče kar fino, če bi se ji lahko čimbolj izognili. Recimo, da želimo vse MP3 datoteke skupine KoRn iz prejšnjega primera razporediti po datumu, ko smo jih dodali v svojo zbirko. Ta podatek smo si zapisali v polje datum. Naredili bomo nekaj v tem stilu:

SELECT
*
FROM
mp3
WHERE
izv_id = 423
ORDER BY
datum;

EXPLAIN poizvedbe nam prikaze naslednji rezultat:

-
id: 1
select_type: SIMPLE
table: mp3
type: ref
possible_keys: izv_id
key: izv_id
key_len: 4
ref: const
rows: 20
Extra: Using where; Using filesort

Ena zadeva tukaj ne štima, spet se je pojavil Using where, pa čeprav smo izkoristili indeks. Malo raziskovanja me pripelje do tega, da je to očitno bug v MySQL, pa ga zaenkrat kar ignorirajmo, ostali podatki vseeno kažejo na to, da je poizvedba uporabila indeks.

Osredotočimo se raje na tisti Using filesort. MySQL v svoji dokumentaciji pravi o tem naslednje:

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

Ta extra pass seveda zahteva svoj čas in upočasnjuje poizvedbo. Kako nam tukaj lahko pomaga indeks? Enostavno, v indeks izv_id dodamo še polje datum, takole:

ALTER TABLE mp3 DROP INDEX izv_id,
ADD INDEX izv_id (izv_id, datum);

Poskusimo še enkrat EXPLAIN in dobimo:

-
id: 1
select_type: SIMPLE
table: mp3
type: ref
possible_keys: izv_id
key: izv_id
key_len: 4
ref: const
rows: 20
Extra: Using where

Tisti Using where nam sicer še vedno smeti, ampak smo se že zgoraj odločili, da ga bomo ignorirali, tisto, kar smo pa hoteli, da izgine, je pa res izginilo.

Še nekaj lahko opravimo zelo hitro, ugotovimo prvi datum in zadnji datum. Preizkusimo EXPLAIN naslednje poizvedbe:

SELECT
MAX(datum)
FROM
mp3
WHERE
izv_id = 423;

Najprej, če imamo indeks le na polju izv_id:

-
id: 1
select_type: SIMPLE
table: mp3
type: ref
possible_keys: izv_id
key: izv_id
key_len: 4
ref: const
rows: 20
Extra: Using where

Da poizvedba najde največjo vrednost v polju datum mora seveda pregledati vseh 20 zapisov. Dodajmo indeksu še polje datum in si oglejmo rezultat EXPLAINa:

-
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

MySQL dokumentacija pravi o tem naslednje:

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

Očitno nam je uspelo, kajne?

1.
3. februar 2008, 16:25

Hehe, ni kaj še en super post.

2.
3. februar 2008, 19:24

Kul index magic!

3.
24. marec 2008, 10:48

Zanimivo in uporabno.