Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?

[es] :: MySQL :: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?

[ Pregleda: 2117 | Odgovora: 8 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

Aleksandar Ružičić
Software Architect, Appricot d.o.o.
Beograd

Član broj: 26939
Poruke: 2881

Jabber: krckoorascic@gmail.com
Sajt: krcko.net


+44 Profil

icon SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 11:58 - pre 176 meseci
Situacija je sledeca, imam tabelu Team:
Code:

 CREATE TABLE `Team` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tag_id` int(10) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `type` tinyint(4) NOT NULL default '0',
  `desc` text,
  `flag` varchar(200) default NULL,
  `city` varchar(80) default NULL,
  `country` varchar(50) default NULL,
  `status` tinyint(4) NOT NULL default '0',
  `sport_id` int(10) unsigned NOT NULL,
  `metadata` text,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `status` (`status`),
  KEY `sport_id` (`sport_id`),
  KEY `tag_id` (`tag_id`),
  KEY `location` (`city`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=utf8 COMMENT='Team entity'


i potrebno je na osnovu query stringa izvuci odredjene timove, query je comma-separated lista kriterijuma koji treba da se zadovolje. polja koja se pretrazuju su name, city i country. S tim da name mora da se pretrazuje sa LIKE (% na kraju).

primer query stringa: inter
kao rezultat izbacuje (trenutno): Inter i Internacional

dok npr ovaj query: inter, brazil
izbacuje samo: Internacional

SQL upit kojim izvlacim se kontruise u PHP-u i za prvi primer izgleda ovako:
Code:

SELECT t.* FROM Team AS t WHERE ('inter' IN (t.country, t.city)) OR (t.name LIKE 'inter%') AND (t.status IN ('1')) LIMIT 0,10


visak zagrada i integer pod navodnicima su posledica Query Buildera koji (moram da) koristim...

za drugi upit je sql query ovakav:
Code:

SELECT t.* FROM Team AS t WHERE ('inter' IN (t.country, t.city)) OR (t.name LIKE 'inter%') AND ('brazil' IN (t.country, t.city)) OR (t.name LIKE 'brazil%') AND (t.status IN ('1')) LIMIT 0,10


posto je meni ovakav upit sumnjivo izgledao (po pitanju koriscenja indexa) uradio sam EXPLAIN:
Code:

mysql> EXPLAIN SELECT t.* FROM Team AS t WHERE ('inter' IN (t.country, t.city)) OR (t.name LIKE 'inter%') AND (t.status IN ('1')) LIMIT 0,10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | name,status   | NULL | NULL    | NULL |  343 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


dakle, indeksi se ne koriste...

interesuje me da li neko ima ideju kako bih mogao da optimizujem ovo?

jedino sto meni pada na pamet to je da city i country izdvojim u dve tabele i da mi ta polja onda budu INT, to ce ubrzati pretragu, ali nisam siguran koliko...


hvala u napred
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 12:40 - pre 176 meseci
LIKE ne ume bas da koristi index,
'inter' IN (t.country, t.city) ne ume da koristi index


sta znaci "moram da koristim query builder" posto ako moras da ih generises tim $#%&#$ to ti je sto ti je?


probaj explain za ovakav upit:

SELECT t.* FROM Team AS t WHERE t.status =1 AND t.country = 'inter' OR, t.city = 'inter' OR t.country='brazil' OR t.city='brazil' OR t.name LIKE 'brazil%' OR t.name LIKE 'inter%' LIMIT 0,10

sa kompizotnim kljucem
alter table t add key i_test (`status`,`country`,`city`,`name`);

mada generalno, onaj like ti pravi najveci problem ...

da, moguce da sam "promenio funkciju queria" posto nisam bas skontao te relacije ... (zasto ne i inter u drugom rezultatu) .. no mislim da kapiras sta sam teo ... par dodatnih zagrada i and umesto or ce vratiti to na kako treba a nece uticati na explain

dalje ... ako pogledas tvoj explain, kaze da su possible key's name i status ... status nije odlucio da koristi verovatno iz razloga sto mu statistika tabele kaze da vise od 70% tabele ima status=1, name ne koristi zato sto nije zaljubljen u like a sve zajedno - ne trudi se previse posto ukupno gleda u 300 redova ... da li je to samo sitni test ili i produkciona tabela ima tako malo redova posto na tu sicu statistika nema mnogo smisla i brze mu ja da iskesira tabelu i odradi table scan u ramu nego da se cima i cita indexe koji nisu nista manji od same tabele?
 
Odgovor na temu

Aleksandar Ružičić
Software Architect, Appricot d.o.o.
Beograd

Član broj: 26939
Poruke: 2881

Jabber: krckoorascic@gmail.com
Sajt: krcko.net


+44 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 13:00 - pre 176 meseci
prvo da ti se zahvalim na brzom odgovoru,

Citat:
bogdan.kecman: LIKE ne ume bas da koristi index,
'inter' IN (t.country, t.city) ne ume da koristi index

znam da LIKE nije bas dobar sa indexima, ali sam mislio da ako je % samo na kraju da moze da se snadje u vecini situacija (bar sam tako negde procitao, nisam skolovan db engineer :D)

Citat:
bogdan.kecman:
sta znaci "moram da koristim query builder" posto ako moras da ih generises tim $#%&#$ to ti je sto ti je?

pa moram da koristim jer dizajner u templejtu moze da odluci da mu treba jos neki parametar pa ga kasnije doda (recimo trazi sve timove u kojima igraju igraci iz srbije...) u templejt a query builder ubaci to u sql upit.
mada, verovatno bih mogao i da izolujem ovaj konkretni slucaj jer dizajneri sigurno nece traziti bilo sta sto ima LIKE (to sam im zabranio :p), to mi jeste veci posao ali izgleda kao bolje resenje...

Citat:
bogdan.kecman:
probaj explain za ovakav upit:

SELECT t.* FROM Team AS t WHERE t.status =1 AND t.country = 'inter' OR, t.city = 'inter' OR t.country='brazil' OR t.city='brazil' OR t.name LIKE 'brazil%' OR t.name LIKE 'inter%' LIMIT 0,10

sa kompizotnim kljucem
alter table t add key i_test (`status`,`country`,`city`,`name`);


to mi ne vraca ono sto mi treba, vraca sve timove iz brazila i sve koji pocinju sa inter ili brazil

Citat:
bogdan.kecman:
da, moguce da sam "promenio funkciju queria" posto nisam bas skontao te relacije ... (zasto ne i inter u drugom rezultatu) .. no mislim da kapiras sta sam teo ... par dodatnih zagrada i and umesto or ce vratiti to na kako treba a nece uticati na explain


u drugom rezultatu ne treba inter da bude jer on nije iz brazila (vec iz italije)

a onaj tvoj kveri sa AND-om i zagradama bi izgledao ovako:
Code:

SELECT t.* FROM Team AS t WHERE t.status =1 AND (t.country = 'inter' OR t.city = 'inter' OR t.name LIKE 'inter%') AND (t.country='brazil' OR  t.city='brazil' OR  t.name LIKE 'brazil%') LIMIT 0,10


sto vraca ok rezultate, explain tog upita daje bolje rezultate:
Code:

+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys               | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | name,status,location,i_test | status | 1       | const |  138 | Using where |
+----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-------------+


bar je prepolovio broj redova kroz koje prolazi

Citat:

dalje ... ako pogledas tvoj explain, kaze da su possible key's name i status ... status nije odlucio da koristi verovatno iz razloga sto mu statistika tabele kaze da vise od 70% tabele ima status=1, name ne koristi zato sto nije zaljubljen u like a sve zajedno - ne trudi se previse posto ukupno gleda u 300 redova ... da li je to samo sitni test ili i produkciona tabela ima tako malo redova posto na tu sicu statistika nema mnogo smisla i brze mu ja da iskesira tabelu i odradi table scan u ramu nego da se cima i cita indexe koji nisu nista manji od same tabele?

ovo trenutno je test server, ali mislim da sam broj redova u ovoj tabeli nece preci 1000, bar ne kod ovog klijenta... mada sistem mora da podrzi i do 20000 timova u bazi...
sto se statusa tice, da, trenutno mislim da samo jedan tim ima status != 1 (ovo polje kontrolise visible/invisible na frontendu)


hvala na odgovoru, videcu sta mogu da uradim... ako imas jos neki predlog (ili neko drugi) napisi slobodno :)
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 14:07 - pre 176 meseci
"ponekad" ume da koristi index kada je % samo na kraju. zato sam i dodao name kao poslednju stavku u kompozitni index, pa ako iskoristi - super :)

sto se tice rezultata .. samo je sada dodao status da koristi .. probaj da odradis force na i_test kljuc ... mada ... bilo bi zgodno kada bi dopunio tabelu sa malo vise slogova :D i kada bi odradio pre upita "analyze table t" ...

vidi sta ce da kaze ako forsujes index, mada taj upit se verovatno izvrsava 0sec kako god?
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 14:28 - pre 176 meseci
Ja bih u tvom slučaju pre koristio dodatnu tabelu KEYWORDS koja bi u sebi imala polja KEYWORD (vrednosti bi se punile iz team.country, team.city, team.name) i TEAM_ID, gde bi bio team.id koji ima odgovorajuću reč. Nad poljem KEYWORD bi mogao da bude index (cela kombinacija (KEYWORD, TEAM_ID) bi mogla da bude UNIQUE...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 14:48 - pre 176 meseci
Citat:
djoka_l: Ja bih u tvom slučaju pre koristio dodatnu tabelu KEYWORDS koja bi u sebi imala polja KEYWORD (vrednosti bi se punile iz team.country, team.city, team.name) i TEAM_ID, gde bi bio team.id koji ima odgovorajuću reč. Nad poljem KEYWORD bi mogao da bude index (cela kombinacija (KEYWORD, TEAM_ID) bi mogla da bude UNIQUE...


obzirom na to da su sve stringovi, ta tabela bi mogla da bude myisam i da ima full text key ... a mogla bi da se puni trigerom iz glavne tabele ..

elem, osnovna zezancija je da na ~300 slogova mysql optimizer ignorise 99% stvari posto mu citanje indexa nije nista brze nego citanje cele tabele ... cela ta tabela je verovatno stala u jednu/dve stranice tablespace-a
 
Odgovor na temu

zgas

Član broj: 74613
Poruke: 334
*.dialup.neobee.net.

Sajt: odbrojavanje.com


Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?29.09.2009. u 21:38 - pre 176 meseci
Koliko mi je poznato kada se koristi OR u WHERE onda se moze koristiti maksimalno jedan indeks.

Neka me neko ispravi ako gresim.
Aukcije, www.Odbrojavanje.com
Aukcija može biti osvojena jeftino - za 30 sekundi a 6 dinara!
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?30.09.2009. u 10:37 - pre 176 meseci
Citat:
bogdan.kecman: obzirom na to da su sve stringovi, ta tabela bi mogla da bude myisam i da ima full text key ... a mogla bi da se puni trigerom iz glavne tabele ..

elem, osnovna zezancija je da na ~300 slogova mysql optimizer ignorise 99% stvari posto mu citanje indexa nije nista brze nego citanje cele tabele ... cela ta tabela je verovatno stala u jednu/dve stranice tablespace-a


Naravno, Bogdan je ovde autoritet za MySql, moj je doprinos samo teorijski. Ja bih, naravno, koristio Oracle Ultrasearch
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs


+2377 Profil

icon Re: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?30.09.2009. u 11:59 - pre 176 meseci
zgas, generalno da, zato je potrebno da imas kompozitni index posto index merge nece da radi ... u principu - uvek je bolje da imas kompozitni index

djoka_l, ma kakav autoritet, ja samo ne krijem nista, ima ovde ljudi jos koji znaju nego se boje da ce neko slucajno nesto da nauci pa da im postane konkurencija .... meni je posao mysql cluster, koji se potpuno drugacije ponasa i koji ima kompletno drugi set problema i odgovora u odnosu na myisam i innodb :) ... ovo za "obican mysql" su sve vise manje opsta mesta
 
Odgovor na temu

[es] :: MySQL :: SELECT po vise polja (i vise kriterijuma) + LIKE - koriscenje indexa?

[ Pregleda: 2117 | Odgovora: 8 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.