DevProTalk

Forumi IT profesionalaca
web development, web design, e-business, SEO


Idite nazad   DevProTalk > Web development i web aplikacije > SQL baze podataka - Sponzor: Baze-Podataka.net
Želite da se reklamirate ekskluzivno na ovoj poziciji? Javite se

SQL baze podataka - Sponzor: Baze-Podataka.net MySQL, MSSQL, Oracle, Access, ODBC. Ako imate problem brže i preciznije ćete dobiti odgovor ako priložite strukturu tabela ili skript koji kreira tabele i puni ih test podacima umesto što to problem opisujete samo rečima. Sponzor: Baze-Podataka.net - Blog o bazama podataka

Odgovori
 
Alati teme Način prikaza
Staro 15. 06. 2007.   #1
dinke
Super Moderator
Invented the damn thing
 
Avatar dinke
 
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
dinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamen
Default MySQL - Optimizacija querija

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

Kôd:
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:

Kôd:
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).
__________________
Caught in a Web|Blogodak
With great power comes great responsibility!
dinke je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #2
zextra
Boris
Grand Master
 
Avatar zextra
 
Datum učlanjenja: 01.12.2005
Lokacija: Novi Sad
Poruke: 775
Hvala: 5
156 "Hvala" u 2 poruka
zextra is on a distinguished roadzextra is on a distinguished road
Default

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.
__________________
"It’s important to have goals when you pet. Otherwise you’re just rubbing another mammal for no reason." - Scott Adams

Poslednja izmena od zextra : 15. 06. 2007. u 13:29.
zextra je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #3
dinke
Super Moderator
Invented the damn thing
 
Avatar dinke
 
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
dinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamen
Default

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:

Kôd:
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.
__________________
Caught in a Web|Blogodak
With great power comes great responsibility!

Poslednja izmena od dinke : 15. 06. 2007. u 14:36.
dinke je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #4
marinowski
Igor Marinović
Expert
 
Avatar marinowski
 
Datum učlanjenja: 09.06.2005
Lokacija: Palić
Poruke: 549
Hvala: 31
39 "Hvala" u 17 poruka
marinowski is on a distinguished road
Pošaljite ICQ poruku za marinowski
Default

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.
__________________
marinowski.com
marinowski je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #5
dinke
Super Moderator
Invented the damn thing
 
Avatar dinke
 
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
dinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamen
Default

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

edit
Cisto da vidis o cemu pricam

Kôd:
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/
__________________
Caught in a Web|Blogodak
With great power comes great responsibility!

Poslednja izmena od dinke : 15. 06. 2007. u 15:35.
dinke je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #6
zextra
Boris
Grand Master
 
Avatar zextra
 
Datum učlanjenja: 01.12.2005
Lokacija: Novi Sad
Poruke: 775
Hvala: 5
156 "Hvala" u 2 poruka
zextra is on a distinguished roadzextra is on a distinguished road
Default

@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?
__________________
"It’s important to have goals when you pet. Otherwise you’re just rubbing another mammal for no reason." - Scott Adams
zextra je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #7
dinke
Super Moderator
Invented the damn thing
 
Avatar dinke
 
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
dinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamendinke je pravi dragi kamen
Default

Odradio sam predlozenu proceduru, ali bez vidljivih rezultata:

Kôd:
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:

Kôd:
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)
__________________
Caught in a Web|Blogodak
With great power comes great responsibility!

Poslednja izmena od dinke : 15. 06. 2007. u 17:26.
dinke je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #8
ivanhoe
Ivan Dilber
Sir Write-a-Lot
 
Avatar ivanhoe
 
Datum učlanjenja: 18.10.2005
Lokacija: Bgd
Poruke: 5.320
Hvala: 104
2.344 "Hvala" u 583 poruka
ivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svimaivanhoe je ime poznato svima
Pošaljite poruku preko Skype™ za ivanhoe
Default

dve ideje koje mozda pomognu (iz mysql manuala):
Citat:
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...
__________________
Leadership is the art of getting people to want to do what you know must be done.
ivanhoe je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #9
MorenoArdohain
Knowledge base
Wrote a book
 
Avatar MorenoArdohain
 
Datum učlanjenja: 16.06.2005
Lokacija: Novi Sad
Poruke: 1.437
Hvala: 37
131 "Hvala" u 82 poruka
MorenoArdohain će postati "faca" uskoroMorenoArdohain će postati "faca" uskoro
Default

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.
__________________
Năo quero mais seguir um só caminho
MorenoArdohain je offline   Odgovorite uz citat
Staro 15. 06. 2007.   #10
zextra
Boris
Grand Master
 
Avatar zextra
 
Datum učlanjenja: 01.12.2005
Lokacija: Novi Sad
Poruke: 775
Hvala: 5
156 "Hvala" u 2 poruka
zextra is on a distinguished roadzextra is on a distinguished road
Default

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.
__________________
"It’s important to have goals when you pet. Otherwise you’re just rubbing another mammal for no reason." - Scott Adams
zextra je offline   Odgovorite uz citat
Odgovori


Alati teme
Način prikaza

Pravila pisanja
Možete ne započinjati nove teme
Možete ne slati odgovore
Možete ne slati priloge
Možete ne izmeniti svoje poruke
vB kôd je Uključen
Smajliji su Uključen
[IMG] kod je Uključen
HTML kôd je Isključen
Pogledajte forum

Slične teme
Tema Početna poruka teme Forum Odgovori Poslednja poruka
[MySQL] Optimizacija upita, indexi mb_sa SQL baze podataka - Sponzor: Baze-Podataka.net 15 30. 08. 2009. 17:47
MySQL optimizacija bluesman SQL baze podataka - Sponzor: Baze-Podataka.net 2 27. 02. 2009. 16:47
CSS optimizacija Vladimir Nikolic (X)HTML, JavaScript, DHTML, XML, CSS 25 23. 08. 2006. 22:54
PHP optimizacija bluesman PHP 10 16. 05. 2006. 02:04
optimizacija koda? Blood SQL baze podataka - Sponzor: Baze-Podataka.net 12 26. 03. 2006. 18:53


Vreme je GMT +2. Trenutno vreme je 00:32.


Powered by vBulletin® Verzija 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright © DevProTalk. All Rights Reserved.
Mišljenja, saveti, izjave, ponude ili druge informacije ili sadržaji nastali na Sajtu su vlasništvo onoga ko ih je kreirao, a ne DevProTalk.com, tako da ne morate da se oslanjate na njih.
Autori poruka su jedini odgovorni za ovakve sadržaje. DevProTalk.com ne garantuje tačnost, kompletnost ili upotrebnu vrednost informacija, stavova, saveta ili datih izjava. Ne postoje uslovi pod kojima bi mi bili odgovorni za štetu ili gubitak koji je posledica bilo čijeg oslanjanja na nepouzdane informacije, ili bilo kakve informacije nastale kroz komunikaciju između registrovanih članova.
Web sajt može sadržavati linkove na druge web sajtove na Internetu ili neke druge sadržaje. Ne kontrolišemo niti podržavamo te druge web sajtove, niti smo pregledali bilo kakve sadržaje na takvim sajtovima. Mi nećemo biti odgovorni za legalnost, tačnost ili prikladnost bilo kog sadržaja, oglasa, proizvoda, usluga ili informacije lociranim na ili distribuiranih kroz druge web sajtove, niti za bilo kakvu štetu nastalu kao posledica takvih informacija. DevProTalk.com drži i čuva druga prava vlasništva na web sajtu. Web sajt sadrže materijale zaštićene copyright-om, zaštitne znakove i druge informacije o pravu vlasništva ili softver. Članovi mogu poslatu informacije zaštićene pravima vlasništva njihovih nosilaca i ona ostaju zaštićena bez obzira da li su oni koji prenose te informacije to naveli ili ne. Osim informacija koje su u javnom vlasništvu ili za koje dobijete dozvolu, nemate pravo da kopirate, modifikujete ili na bilo koji način menjate, objavljujete, prenosite, distribuirate, izvršavate, prikazujete ili prodajte bilo koju informaciju zaštićenu pravima vlasništva. Slanjem informacija ili sadržaja na bilo koji deo DevProTalk.com, Vi automatski dozvoljavate i predstavljate garanciju da imate pravo da dozvolite DevProTalk.com ili članovima DevProTalk.com bespovratnu, kontinualnu, neograničenu, globalnu dozvolu da koriste, kopiraju, izvršavaju, prikazuju i distribuiraju takve informacije i sadržaje i da iz takvih sadžaja koriste bilo koji deo u bilo koje svrhe, kao i pravo i dozvolu da koriste gore navedene sadržaje. Svi zaštitni znakovi (trademarks), logotipi, oznake usluga, firme ili imena proizvoda koji se pominju na ovom web sajtu su vlasništvo kojim raspolažu njihovi vlasnici.