MySQL Indexes OR

Roze · September 12, 2005

Sveiki, tēmai gan nav nekāda sakara ar php, bet tīri DB-related, taču tākā šeit tusējas gudri cilvēki un ar php iet roku rokā ar db tad tomēr pajautāšu (nedaudz tika apcilāta arī irc @ #php.lv ) :

Jautājums ir kapēc MySQL neizmanto indexus pie OR? Jebšu vai kāds var izskaidrot sekojošu lietu (indexi gan uz uid1, gan uid2, gan testa peec arii uz abiem... ):

mysql> explain SELECT * FROM some_tables WHERE uid1 = 107871 OR uid2 = 107871;

+-------------+------+----------------+------+---------+------+-------+-------------+

+-------------+------+----------------+------+---------+------+-------+-------------+

+-------------+------+----------------+------+---------+------+-------+-------------+

mysql> explain SELECT * FROM some_tables WHERE uid1=107871 union select * FROM some_tables WHERE uid2=107871;

+-------------+------+---------------+------+---------+-------+------+-------------+

+-------------+------+---------------+------+---------+-------+------+-------------+

| some_tables | ref | uid1 | uid1 | 4 | const | 2 | Using where |

| some_tables | ref | uid2 | uid2 | 4 | const | 1 | Using where |

+-------------+------+---------------+------+---------+-------+------+-------------+

Kā var redzēt pirmajā variantā pie OR rowu skaits ko MySQL uzskata ka būtu jāiet cauri lai atrastu rezultātu ir 30530 tai pašā laikā sadalot kveriju un pēctam apvienojot ar union redzam ka reāli sanāk tikai 2+1.

Pēc MySQL manuāļa nekas tāds nav minēts: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html (tikai pavecā 2003. gada komentārā ko analogu var ieraudzīt)

Līdz ar to pirms sūtīt kļauzu uz MySQL (tas tak nav normāli ne) jūsu domas?

Grey_Wolf · September 12, 2005

viss pareizi ja nekljuudos tad

OR ir logiskaa DB apviennoskana a OR b true= ja viens no operandiem ir true

izmanto XOR

XOR ir DB apvienoshana pee principa VAI a XOR b true= ja viens no operandiem ir true

--------

skiet ka tad tiks izmantots Index

---------

P.S. varbuut kljuudos bet skiet ka vajadzeetu taa buut

labots: pat no tava noraadiitaa linka ;)

------

These WHERE clauses do not use indexes:

/* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

/* Index is not used in both parts of the WHERE clause */

... WHERE index=1 OR A=10

/* No index spans all rows */

... WHERE index_part1=1 OR index_part2=10

-------------

Edited September 12, 2005 by Grey_Wolf

bubu · September 12, 2005

Gray_Wolf:

OR nav nekāda loģiskā apvienšana. Tas ir loģiskais VAI.

XOR var pat nederēt šajā gadījumā! (jo true XOR true = FALSE)

Pie tam, no tavis pat teksta:

/* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10

Rozei IR indekss.

No tā paša linka:

The following WHERE clauses use indexes:
... WHERE index=1 OR A=10 AND index=2

/* optimized like "index_part1='hello'" */

(and konstante netraucē)

Tur jau ir tas sāls!

Pie tam, tika novērots, ka man šāda konstrukcija izmanto indeksu:

mysql> explain select * from x where a=1 or b=2;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                   |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | x     | index | i,i1,i2       | i    |      10 | NULL |   26   | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Edited September 12, 2005 by bubu

Roze · September 12, 2005

Pie kam problēma jau ir arī tajā ja bubu variantā tiek parādīts ka izmantots indekss 'i', tad rowi tikuntā ir tiek izsearchots cauri veseli 26 (ko no bubu noskaidroju ka tas ir visas DB saturs) ja izpilda vienkaarshi

select * from x where a=1 or b=2;

atgrieztais rowu skaits ir 3 liidz ar to MySQLs ir izdariijis krietni daudz lieka..

Varjaubūt ka explain strādā savādāk un pareizi nerāda shēmu pēc kā MySQls kaut ko darīs, no viena komentāra:

"EXPLAIN appears to be empirical.

EXPLAIN does not look at

an index and a select and determine the potential use of

the index. Instead EXPLAIN appears to look at the actual

data and determine the actual use of the index. In a development

database this becomes problematic requiring the generations

of representative data. Occasionally it is difficult to predict

how the data will effect the application of an index and it

is not clear that your index does not support your select or

your test data does not exercise the index.

More details of what to expect from EXPLAIN should be included

in the document."

labots: pat no tava noraadiitaa linka
------

These WHERE clauses do not use indexes:

/* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

/* Index is not used in both parts of the WHERE clause */

... WHERE index=1 OR A=10

/* No index spans all rows */

... WHERE index_part1=1 OR index_part2=10

Neviens jau no šiem variantiem neatbilst manējam.. (šeit parādīti kā darbojas kopējie indeksi no kreisās uz labo).

1. variantā izstrūkst itkā vidējā lauka salīdzināšana (index_part2)

2. tiek salīdzināts A= (lai gan nezkapēc tikuntā query optimizeris daļu rezultātu varēja ielasīt no index tabulas taču iespējams vēlāk ir problēmas ar sajoinoshanu)

3. šis netiek lietots tāpēc ka uz tabulas nav neviena indexa kur 'index_part2' lauks būtu pirmais..

Manā variantā indekss ir uz katra lauka.

XOR pēc loģikas būtu jaizpildās tikai vienam no kritērijiem bet otram obligāti nē, diezvai tur kas mainās no pieprasījuma būtības.

marrtins · October 18, 2007

uid1 = 107871 OR uid2 = 107871 indexu nevar izmantot, jo

1) izmantot index uz tikai uid1 - nav jēgas, tāpat jāskanē vēl pēc uid2

2) izmantot index uz uid1 un uid2 kopā - nav jēgas, jo indeksā uid2 ir sakārtots pēc uid1 (ar AND starpā - uķipuķi smuki buci)

3) izmantot index uz tikai uid1 un index uz tikai uid2 kā atseviški indexi - MySQL prot izmantot tikai vienu indexu vienam selektam/joinam (iespējams, citās DBVS ir savādāk)

Tas, ka šo pieprasīju vienkārši var pārvērst par union, iespējams, ir MySQL query optimiziera speciālgadījums (nepilnība?).

Delfins · October 19, 2007

Liekas visās DB tā ir... ja vien nav partitioning !?

Roze · October 19, 2007

uid1 = 107871 OR uid2 = 107871 indexu nevar izmantot, jo

Mok vēl vecākus postus nevarēja izrakt? -> Posted 2005. gada 12. Sep 18:04

Gints Plivna · October 19, 2007

Liekas visās DB tā ir... ja vien nav partitioning !?

Nav gan īsti saprotams, kas bija domāts ar "tā", bet nu ja nu tā kā iepriekšējais lietotājs teica, ka "MySQL prot izmantot tikai vienu indexu vienam selektam/joinam (iespējams, citās DBVS ir savādāk)" tad šeit ir tas kā Oracle to dara:

uztaisam tabulu ar 2 number laukiem, pie tam vienā ir unikālas vērtības otrā šajā gadījumā katrai vērtībai atbilst 2 ieraksti:

SQL> create table big (
 2  uid1 number not null,
 3  uid2 number not null,
 4  text varchar2(1000) not null);

Table created.

SQL> insert into big select rownum, mod(rownum, 100), name
 2  from dba_source where rownum <=200;

200 rows created.

Uztaisam indexus, izrēķinam statistikas:

SQL> create index uid1_idx on big (uid1);

Index created.

SQL> create index uid2_idx on big (uid2);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'big');

PL/SQL procedure successfully completed.

palaižam autotraci un selektu:

SQL> set autot traceonly
SQL> select * from big
 2  where uid1 = 1
 3  or uid2 = 2
 4  /


Execution Plan
----------------------------------------------------------
Plan hash value: 1406454030

---------------------------------------------------------------------------------------------
| Id  | Operation						| Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT				 |		  |	 3 |	45 |	 3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID	 | BIG	  |	 3 |	45 |	 3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS	|		  |	   |	   |			|		  |
|   3 |	BITMAP OR					 |		  |	   |	   |			|		  |
|   4 |	 BITMAP CONVERSION FROM ROWIDS|		  |	   |	   |			|		  |
|*  5 |	  INDEX RANGE SCAN			| UID2_IDX |	   |	   |	 1   (0)| 00:00:01 |
|   6 |	 BITMAP CONVERSION FROM ROWIDS|		  |	   |	   |			|		  |
|*  7 |	  INDEX RANGE SCAN			| UID1_IDX |	   |	   |	 1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  5 - access("UID2"=2)
  7 - access("UID1"=1)

Nu tātad šeit ir 2 operācijas BITMAP CONVERSION FROM/TO ROWIDS, kur rindiņu norādes (ROWID) tiek konvertētas uz kaut kādu bitu virkni un tad kā es saprotu (precīzu mehānismu nezinu) ātri var iegūt rezultātu, tipa veicot tikai loģisko OR uz šīm bitu virknītēm un tad atkal konvertē atpakaļ uz ROWIDiem, pēc kuriem jau var iegūt konkrētas rindas tabulā.

Protams tas ir atkarīgs no datu izvietojuma, ja kāda no vērtōbam ir ļoti bieži sastopama, tad full scans būs lētāks:

taisam jaunu tabulu, kas neatšķiras no iepriekšējās, bet atšķiras dati - 2. kolonā būs tikai 2 vērtības 0 un 1

SQL> insert into big1 select rownum, mod(rownum, 2), name
 2  from dba_source where rownum <=200;

200 rows created.

Un tagad plāns ir pavisam citāds, kā arī sagaidāmo rindiņu skaits ir dauidz lielāks protams:

SQL> ed
Wrote file afiedt.buf

 1  select * from big1
 2  where uid1 = 1
 3* or uid2 = 0
SQL> 
SQL> /

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3470735819

--------------------------------------------------------------------------
| Id  | Operation		 | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |   101 |  1515 |	 3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG1 |   101 |  1515 |	 3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("UID2"=0 OR "UID1"=1)

Posts varbūt ne īsti par tēmu, bet iespējams kādam noder ;)

Gints Plivna

http://datubazes.wordpress.com

Grey_Wolf · October 19, 2007

Roze --> a nesi ieverojis ka te taadi saradushies , uzskata par svetu peienakumu atbildeet uz VISIEM postiem ;)

neskatoties cik veci vinji ir.... 'tjipa' dzenam sev reitingu ...

Gints Plivna · October 19, 2007

Roze --> a nesi ieverojis ka te taadi saradushies , uzskata par svetu peienakumu atbildeet uz VISIEM postiem ;)
neskatoties cik veci vinji ir.... 'tjipa' dzenam sev reitingu ...

OOOps sorry, iespējams, ka tas nebija domāts tieši man (kaut gan ja jau dauidzskaitlī, tad droši vien arī man) ;), bet es nepaskatījos, ka oriģinālais posts ir no 2005 gada.

Vienkārši ieraudzīju, ka posts augšā un atbildēju.

Vēlreiz sorry :)

Gints Plivna

http://datubazes.wordpress.com

Grey_Wolf · October 19, 2007

iespējams, ka tas nebija domāts tieši man

Nee nebija gan ;)

te ir citi gudrieniki kas rok augsaa vecus Topikus...

P.S. vispar paldies intresants un ismeljosh viedoklis...

marrtins · October 19, 2007

Roze --> a nesi ieverojis ka te taadi saradushies , uzskata par svetu peienakumu atbildeet uz VISIEM postiem ;)
neskatoties cik veci vinji ir.... 'tjipa' dzenam sev reitingu ...

Tu par ko runāt? Sajūta, ka par mani, bet taču nē? Ja tomēr, tad varam padiskutēt... Un kāds vēl fukken reitings? Tu domā postu skaitu? Tad paskaties labāk uz tiem gudreļiem, kas tik izsmeļoši atbild visiem topikiem, `probleeeeema`, `a man neiet`, `a man paziņojums DB ERROR, ko darīt?`, `neprotu nekā, bet nesanāk`

Roze, un tad? Pamanīju interesantu tēmu (kas šeit ir ļoooti maz) un ierakstīju. Kādas problēmas?

andrisp · October 19, 2007

Tad paskaties labāk uz tiem gudreļiem, kas tik izsmeļoši atbild visiem topikiem, `probleeeeema`, `a man neiet`, `a man paziņojums DB ERROR, ko darīt?`, `neprotu nekā, bet nesanāk`

Tagad man liekas, ka tu runā par mani. :) Labi, nemēģināšu taisnoties, kāpēc atbildu lielākoties uz visiem jautājumiem, kur man ir ko teikt.

Edited October 19, 2007 by andrisp

Roze · October 19, 2007

Roze, un tad? Pamanīju interesantu tēmu (kas šeit ir ļoooti maz) un ierakstīju. Kādas problēmas?

Tādas, ka kopš kādas MySQL 5.0.2x versijas šī problēma vairs nav ne aktuāla ne interesanta.

Tagad tas izskatās apmēram šādi:

mysql> EXPLAIN SELECT *  FROM tabula WHERE uid1 = 2323 OR uid2= 234234;
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| id | select_type | table | type		| possible_keys | key		   | key_len | ref  | rows | Extra								   |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
|  1 | SIMPLE	  | tabula| index_merge | PRIMARY,uid2  | PRIMARY,uid2  | 4,152   | NULL |	2 | Using union(PRIMARY,uid2); Using where  |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
1 row in set (0.00 sec)

Līdz ar to uniona izmantošana ir sekundāra..

Tākā nav ko rakt vecus topicus (ja neesi pārbaudijis vai problēma vēl eksistē.. un principā nepasaki neko jaunu kā tikai atkārto to kas sākotnējā topicā jau ir) :)

Proti divu gadu laikā mainās gan produkti gan cilvēku zināšanas :)

p.s.

Nav gan īsti saprotams, kas bija domāts ar "tā", bet nu ja nu tā kā iepriekšējais lietotājs teica, ka "MySQL prot izmantot tikai vienu indexu vienam selektam/joinam (iespējams, citās DBVS ir savādāk)"

Šis gan aizvien ir patiesi.. MySQL prot kverijā izmantot faktiski tikai 1 indeksu (manā piemērā gan māk concatenot divus) :)

Sign In

MySQL Indexes OR

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites