spravne oindexovani (II)

[Honza Pazdziora]

On Thu, 14 Sep 2000 10:39:36 +0200, Ales Pour <pour na princip.cz> wrote:
> 
> stat SMALLINT,id SMALLINT,t INT,"neco",PRIMARY KEY (id,t,stat)
> 
> typicky dotaz:
> 
> SELECT "neco",t FROM tabulka WHERE id=n AND t vetsi/mensi/mezi ORDER BY
> t ASC/DESC [LIMIT n]
> 
> A ted, EXPLAIN vypada takhle (tabulka ma cca 370 tisic zaznamu, soubor
> ma velikost cca 10 MB):
> 
> mysql> EXPLAIN SELECT x,y,t FROM ls_v_n WHERE id=1 ORDER BY t DESC LIMIT
> 1;
> +--------+------+---------------+---------+---------+------+------+-------+
> | table  | type | possible_keys | key     | key_len | ref  | rows |
> Extra |
> +--------+------+---------------+---------+---------+------+------+-------+
> | ls_v_n | ref  | PRIMARY       | PRIMARY |       2 | ???  | 3689
> |       |
> +--------+------+---------------+---------+---------+------+------+-------+
> 
> To nevypada spatne, ale cas zpracovani tohodle dotaz je bez ohledu na
> poradi hledanych sloupcu, zpresnovani casoveho kriteria, razeni podle
> casu nebo pridani samostaneho indexu na t konstantni, a to okolo 30
> sekund.
> Odstranil jsem ten primarni klic a funguje to STEJNE rychle!

Jake jsou casy dotazu

	select id from tabulka where id = ?
	select id from tabulka where id = ? order by t

? Kazdopadne vysledek ma tech 3689 zaznamu, to je pravda?

> Kdyz vytvorim klic PRIMARY KEY(t,id,stat), tak ASC razeni trva okolo
> 12-17 sekund, DESC razeni bez vymezeni casu t (vetsi/mensi/mezi) trva
> cca 3-5 sekund, ale s vymezenim skoro 2 minuty. Pritom EXPLAIN je:
> 
> mysql> EXPLAIN SELECT x,y,t,rst FROM ls_v_n WHERE s_id=1 ORDER BY t DESC
> LIMIT 1;
> +--------+------+---------------+------+---------+------+--------+------------+
> | table  | type | possible_keys | key  | key_len | ref  | rows   |
> Extra      |
> +--------+------+---------------+------+---------+------+--------+------------+
> | ls_v_n | ALL  | NULL          | NULL |    NULL | NULL | 368913 | where
> used |
> +--------+------+---------------+------+---------+------+--------+------------+
> 
> Je to OK? 

Vzhledem k tomu, ze v tom dotazu neni ve where pouzit sloupec t, je
type ALL v poradku, proste projde celou tabulku.

> Jde tuhle tabulku oindexvat lip? DIKY!

A na jake vlastne dotazy ji chcete optimalizovat?

> P.S. Ten stroj na kterem MySQL bezi je pekna sunka (486/16MB,Linux), ale
> alespon jsou videt rozdily.

Ano, to by odpovidalo tomu, ze setridit 3k zaznamu mu vezme pulminuty,
to by mohlo byt.

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
   .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain.
Petition for a Software Patent Free Europe http://petition.eurolinux.org
------------------------------------------------------------------------