MySQL - Optimizacija querija

[dinke]

Imam relativno veliku tabelu sa par stotina hiljada do par miliona domena. Struktura je sledeca:


CREATE TABLE `odm_result_keywords_de` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain` varchar(255) NOT NULL DEFAULT '',
`ovt_ext` int(11) DEFAULT NULL,
`ovt_ext_local` int(11) DEFAULT NULL,
`whois_status` varchar(100) DEFAULT NULL,
`last_modified_date` date DEFAULT NULL,
`domain_status` varchar(100) DEFAULT NULL,
`nameserver` varchar(255) NOT NULL DEFAULT '',
`master_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`),
KEY `ovt_ext` (`ovt_ext`),
KEY `ovt_ext_local` (`ovt_ext_local`),
KEY `whois_ovt` (`whois_status`,`ovt_ext`),
KEY `domain_status` (`domain_status`)
)

E sad, posto moram da proveravam na whois samo one domene koji imaju bilo ovt_ext ili ovt_ext_local polja > 0 imam sledeci query u whois check skriptu:

select domain from odm_result_keywords_de
where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0)
Ima li neko ideju kako optimizovati pomenuti query tako da koristi indexe. Ovako kako je sada, koristio bi se index whois_ovt, ali posto je kardinalnost nikakva nije od neke koristi (za 99% domena ovde whois_status polje je null), tako da bi se prakticno proveravali svi slogovi (sto nije mala cifra). Slicno je i sa kardinalnoscu ovt polja (vrednosti su u 90% slucajeva 0).

[zextra]

Edit: Da se ispravim:

Probaj da uklonis ovt_ext index, a da u whois_ovt dodas jos i ovt_ext_local polje.

Sa explain proveri da li se whois_ovt index koristi. U prisustvu ovt_ext indexa, on se koristi umesto whois_ovt.

[dinke]

OK odradio sam sledece:

- kreirao identicnu test tabelu i napunio je sa 15k domena
- dropovao index ovt_ext
- dropovao index whois_ovt
- dodao index whois_ovt_local (whois_status, ovt_ext, ovt_ext_local)

Kao output explaina sada sam dobio:


mysql> explain select id,domain from odm_result_keywords_test where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0) limit 100;
+----+-------------+--------------------------+------+-------------------------------+-----------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------+-------------------------------+-----------------+---------+-------+-------+-------------+
| 1 | SIMPLE | odm_result_keywords_test | ref | ovt_ext_local,whois_ovt_local | whois_ovt_local | 103 | const | 16193 | Using where |
+----+-------------+--------------------------+------+-------------------------------+-----------------+---------+-------+-------+-------------+


sto bi trebalo da znaci da sada koristi index.

Poprilicno sam siguran da sam ovaj index probao i ranije, s tim da nisam dropovao ovt_ext index. Zasto je to neophodno? Inace, kada sam vratio index na ovt_ext nista se u explain izlazu nije promenilo, sto bi trebalo da znaci da ovt_ext ne mora da se dropuje?
Inace, treba mi i index na ovt_ext jer se pored ovog chekiranja ista tabela koristi i iz frontenda (za pretrazivanje). Naravno ima mnogo i upisivanja, pa pokusavam da ne dodajem previse indexa da se ne uspori insert.

Sad cu probati da dodam ovaj index (whois_status, ovt_ext, ovt_ext_local) na jednu pravu tabelu (trichavih 2 miliona slogova) pa cu javiti da li funkcionise. :)

[marinowski]

Meni je pomoglo sledece: postavio indekse kako treba, sa explain query vidim da ne koristi ono sto ja zelim ... Nakon jednog 'optimize table ...' sve je pocelo da leti, tako da optimize toplo preporucujem kod ovakvih slucajeva.

[dinke]

Na ovako velikim tabelama dodavanje indexa traje preeeeeduuugooo... ne smem ni da pomislim koliko bi trajao optimize :)

edit
Cisto da vidis o cemu pricam :)


mysql> alter table odm_result_keywords_es add index whois_ovt_local(whois_status, ovt_ext, ovt_ext_local);
Query OK, 2090574 rows affected (36 min 20.43 sec)
Records: 2090574 Duplicates: 0 Warnings: 0

mysql>


Inace, znam da nekad pomogne i force nekog indexa (use index), kada ga MySQL svejedno ne koristi.

Kad smo vec na temi, sjajan MySQL Optimization Blog:
http://www.mysqlperformanceblog.com/

[zextra]

@dinke: Nisam siguran da je moja predpostavka tacna, ali mozda je mysql ovako gledao: imao si dva indexa, i ni jedan nije bio 100% odgovarajuci za pomenuti select query, ali je ovt_ext bio naveden pre whois_ovt, pa ga je zato mysql preferirao?

Primetih jos nesto: ako probas isti taj whois_ovt_local da definises tako sto ces staviti whois_local polje na kraj, index vise nece biti odgovarajuci. Zasto?

[dinke]

Odradio sam predlozenu proceduru, ali bez vidljivih rezultata:


mysql> select id,domain from odm_result_keywords_es where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0) limit 100;
Empty set (29.15 sec)

mysql> explain select id,domain from odm_result_keywords_es where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0) limit 100;
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
| 1 | SIMPLE | odm_result_keywords_es | ref | ovt_ext,ovt_ext_local,whois_ovt_local | whois_ovt_local | 103 | const | 2088308 | Using where |
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

Dakle, i dalje ne valja :( 30 sekundi traje query, koristi taj novi index, ali ima 2088308 redova sto su skoro svi.

Evo indexa koji sada postoje u tabeli:


mysql> show index from odm_result_keywords_es;
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| odm_result_keywords_es | 0 | PRIMARY | 1 | id | A | 2090574 | NULL | NULL | | BTREE | |
| odm_result_keywords_es | 0 | domain | 1 | domain | A | 2090574 | NULL | NULL | | BTREE | |
| odm_result_keywords_es | 1 | ovt_ext | 1 | ovt_ext | A | 138 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | ovt_ext_local | 1 | ovt_ext_local | A | 77 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 1 | whois_status | A | 2090574 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 2 | ovt_ext | A | 2090574 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 3 | ovt_ext_local | A | 2090574 | NULL | NULL | YES | BTREE | |
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

[ivanhoe]

dve ideje koje mozda pomognu (iz mysql manuala):

To help MySQL better optimize queries, use ANALYZE TABLE or run myisamchk --analyze on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a non-constant expression. You can check the result from the table analysis by using SHOW INDEX FROM tbl_name and examining the Cardinality value. myisamchk --description --verbose shows index distribution information.

To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.


trebalo bi da ovo prvo traje krace nego optimize, a posto upit koristi index trebalo bi da pomogne takodje. Ovo drugo verovatno nema smisla na tako velikoj tabeli...

[MorenoArdohain]

Iz mog iskustva, na tako velikim tabelama, indexi ne pomazu previse.
Zato ih obicno splitujem po nekom kriterijumu, koji omogucava da ukupno vreme pretrage za sve te tabele uvek bude manje od vremena pretrage za jednu u kojoj su svi recordi.
Naravno, to zahteva vise posla oko inserta i update-a istih (i vise select query-ja), ali mislim da vredi.

[zextra]

Pokusaj da izvrsis isti query, s tim sto ces proveravati "whois_status is not null", pa vidi da li se nesto menja (kako si rekao, za 99% zapisa vrednost je null), bar da znas da li index radi kako treba. Ili eksperimentisi sa preostala dva parametra.. Sto je veci dataset iz kog biras podatke, vece je usporenje, ma koliko da ti je dobar index.

[Dejan Topalovic]

Ako ti je za uslove u WHERE klauzuli cardinality visok (npr. veci od 40% od ukupnog broja redova), onda zaboravi na indexe, jer ce u tom slucaju full table scan uvijek biti optimalniji.

Koju verziju mysql-a imas?

[dinke]

Kako na kom serveru, na serveru gde sam ovo testirao je 5.1.18-beta.

[dinke]

E da, samo da javim da sam ovde napravio "workaround", tako sto sam ovaj query razbio na 2 malo jednostavnija kverija.

Dakle, umesto jednog:

select domain from odm_result_keywords_de
where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0)

Sada imam:


select domain from odm_result_keywords_de
where ovt_ext > 0 and whois_status is null

i


select domain from odm_result_keywords_de
where ovt_ext_local > 0 and whois_status is null

U ova dva querija koristi se index na ovt_ext (ovt_ext_local), tako da je zbog niske kardinalnosti (vrlo mali broj slogova ima ovt>0) drasticno manji broj recorda kroz koje upit mora da prodje, sto drasticno ubrzava stvari.