SQL in indeksi (2.del)

February 3, 2008 2 minute read

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?

Vini