[SQL] ivm. indexes

[Verwijderd]

Hoi, ik heb een vraagje over indexes

Als je een tabel A(kolom1, kolom2, ...) hebt, en je maakt een index (of prim. key) op (kolom1, kolom2), ik veronderstel dat het volgende dan klopt:

- query met "WHERE kolom1 = ..." -> index wordt gebruikt
- query met "WHERE kolom1 = ... AND kolom2 = ..." -> index wordt gebruikt
- query met "WHERE kolom2 = ... AND kolom1 = ..." -> index wordt gebruikt
- query met "WHERE kolom2 = ..." -> index wordt niet gebruikt

Van wat hierboven staat, ben ik vrij zeker, maar ik vraag me vooral het volgende af: wordt de index ook gebruikt bij volgende query: "WHERE kolom1 = ... OR kolom2 = ...". Ik ben vrij zeker van wel. Want als dat niet het geval is, kan ik beter 2 aparte indexes (op kolom1 en kolom2) maken denk ik...

thanks

[ Voor 16% gewijzigd door Verwijderd op 16-06-2004 10:15 ]

[whoami]

Nee, die index wordt niet gebruikt met die OR imo. Althans, niet voor alletwee de velden.

Hij zal enkel gebruikt worden bij de search op kolom1, maar niet op kolom2. Daar kan hij nl. niet gebruikt worden, omdat hij eerst moet weten binnen welke waarden voor kolom1 hij moet zoeken. Aangezien je met een OR werkt, lukt dat dus niet.

Om heel zeker te zijn wat er gebeurd, kan je eens het execution plan bekijken.

[Creepy]

En hoe indexen gebruikt worden is ook weer redelijk DB afhankelijk. MySQL bijv. heeft een enigzins vreemd gebruik van indexen

In jou voorbeeld zou gebruikt MySQL alleen de index bij query 1 en 2. 3 niet omdat de volgorde van de velden in de index anders is dan die in de query.

[whoami]

Creepy schreef op 16 juni 2004 @ 11:16:
In jou voorbeeld zou gebruikt MySQL alleen de index bij query 1 en 2. 3 niet omdat de volgorde van de velden in de index anders is dan die in de query.

Meen je dat? De optimizer moet daar toch gewoon voor zorgen dat die index kan gebruikt worden?

[Creepy]

whoami schreef op 16 juni 2004 @ 11:20:
[...]


Meen je dat? De optimizer moet daar toch gewoon voor zorgen dat die index kan gebruikt worden?

Helaas

http://dev.mysql.com/doc/...tiple-column_indexes.html en
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html

En daarbij gebruikt MySQL max. 1 index per tabel.

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

[ Voor 65% gewijzigd door Creepy op 16-06-2004 11:37 ]

[curry684]

SQL Server 2000:

SQL:

select * from TestTable where TestId1 = 5;
select * from TestTable where TestId2 = 5;
select * from TestTable where TestId1 = 5 and TestId2 = 8;
select * from TestTable where TestId1 = 5 or TestId2 = 8;

Levert als execution plan 4 batches van 25% op die allemaal recht de index induiken met een dataset van random velden in beide kolommen.

[whoami]

Ik heb hier geen SQL Server atm, maar ik ben wel benieuwd naar het execution plan van query nr 4.

[curry684]

whoami schreef op 16 juni 2004 @ 12:15:
Ik heb hier geen SQL Server atm, maar ik ben wel benieuwd naar het execution plan van query nr 4.

Ik zeg dus: alle 4 identiek, recht de index in en 25% execution time van de batch

[Creepy]

curry684 schreef op 16 juni 2004 @ 12:47:
[...]

Ik zeg dus: alle 4 identiek, recht de index in en 25% execution time van de batch

Wat betreft de opzimizer heeft MySQL dus nog een (lange) weg te gaan.

Maar dan zou ik, net als whoami, toch het executie plan van die 4de query willen zien. Knap staaltje als je 1 multicolumn index hebt en dan toch de index gebruikt met een OR op de velden van de multicolumn index.

[ Voor 29% gewijzigd door Creepy op 16-06-2004 12:53 ]

[Verwijderd]

hmmm, vaak levert een OR query problemen op met indexen, vaak kan je dan beter je query opsplisten waarbij je je OR er uit haalt en deze ombouwt naar een query met UNION, dit wil nogweleens wat performance opleveren bij grote db's....

[whoami]

curry684 schreef op 16 juni 2004 @ 12:47:
[...]

Ik zeg dus: alle 4 identiek, recht de index in en 25% execution time van de batch

Het lijkt me sterk dat voor testid2 ook een index kan gebruikt worden, aangezien in query nr4 testid1 niets te maken heeft met de mogelijke waarden voor testid2.

[Yoshi|IA2]

MySQL heeft zo goed als geen optimizer...

Iemand bij ons in de richting gaat volgend jaar (een deel van) query optimalisation schrijven voor MySQL in het kader van zijn thesis...

[ Voor 9% gewijzigd door Yoshi|IA2 op 16-06-2004 13:14 ]

[Creepy]

yoshi_rules schreef op 16 juni 2004 @ 13:12:
MySQL heeft zo goed als geen optimizer...

Dat is nou ook wel weer enigzins overdreven

[curry684]

whoami schreef op 16 juni 2004 @ 13:08:
[...]


Het lijkt me sterk dat voor testid2 ook een index kan gebruikt worden, aangezien in query nr4 testid1 niets te maken heeft met de mogelijke waarden voor testid2.

Sja probeer zelf maar uit:

SQL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTable]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
    [TestId1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TestId2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD 
    CONSTRAINT [PK_TestTable] PRIMARY KEY  CLUSTERED 
    (
        [TestId1],
        [TestId2]
    )  ON [PRIMARY] 
GO

SET NOCOUNT ON;
DECLARE @counter int;
SET @counter = 0;
WHILE(@counter != 500)
BEGIN
  INSERT TestTable VALUES (CAST(rand(@counter * 12345) * 50 AS int), 
          CAST(rand(@counter * 654321) * 50 AS int));
  SET @counter = @counter + 1;
END
SET NOCOUNT OFF;

-- Feitelijke testcode
SELECT * FROM TestTable WHERE TestId1 = 5;
SELECT * FROM TestTable WHERE TestId2 = 5;
SELECT * FROM TestTable WHERE TestId1 = 5 AND TestId2 = 8;
SELECT * FROM TestTable WHERE TestId1 = 5 OR TestId2 = 8;

Misschien dat ik iets mis, maar op deze T-SQL code krijg ik exact identieke execution plans op de 4 selects: 1 enkele simpele Clustered Index Scan.

Ik heb om eerlijk te zijn ook geen idee hoe ie het doet, maar ik vind het wel knap

[ACM]

Creepy schreef op 16 juni 2004 @ 11:34:
[...]

Helaas

http://dev.mysql.com/doc/...tiple-column_indexes.html en
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html

En daarbij gebruikt MySQL max. 1 index per tabel.


[...]

Dat lees je verkeerd.

De query met kolom1 = ... and kolom2 = ... is in principe hetzelfde als kolom2 = ... and kolom1 = ... en mysql kan dan ook prima die index gebruiken.

Wat het niet kan, is bijvoorbeeld met een OR-query:
T(kolom1, kolom2) en twee losse indices I1(kolom1), I2(kolom2)
where kolom1 = ... OR kolom2 = ...
Om dan de OR op te splitsen in een soort union van twee losse index-searches, MySQL zal dan de index proberen te gebruiken op de ene helft (welke weet ik niet, hangt vast af van het aantal records dat er uit zou kunnen rollen) en de andere helft van de OR er bij scannen. PostgreSQL kan dit trouwens wel en dat is dan ook een van de typen queries waar het MySQL ver in het stof laat bijten.

en wat ook niet kan is met het gegeven voorbeeld van de TS, om de index te gebruiken voor een select * ... where kolom2 = ...

Creepy schreef op 16 juni 2004 @ 12:51:
Wat betreft de opzimizer heeft MySQL dus nog een (lange) weg te gaan.

't Nadeel van een "betere" optimiser is dat ie automatisch complexer wordt en dat heeft weer tot gevolg dat MySQL zijn snelheidswinst met simpele queries deels verliest

[ Voor 19% gewijzigd door ACM op 16-06-2004 13:54 ]

[djluc]

't Nadeel van een "betere" optimiser is dat ie automatisch complexer wordt en dat heeft weer tot gevolg dat MySQL zijn snelheidswinst met simpele queries deels verliest

Eigenlijk zou MySQL er 2 moeten hebben. Eerst kijken of het een eenvoudig of een moeilijk query is. Bijvoorbeeld gebaseerd op het feit of enkele woorden in de SQL voorkomen en dan de beste kiezen. De minimale overheid van het kiezen tussen de juiste valt dan in het niet.

[Creepy]

ACM schreef op 16 juni 2004 @ 13:52:
[...]

Dat lees je verkeerd.

De query met kolom1 = ... and kolom2 = ... is in principe hetzelfde als kolom2 = ... and kolom1 = ... en mysql kan dan ook prima die index gebruiken.

Uit de MySQL manual:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Dus geen indexed search op (col2, col1) of (col2) i.v.m. leftmost prefix!
Of begrijp ik je nu verkeerd?

[ACM]

PostgreSQL heeft zoiets, de "simpele" planner (is al een stuk geavanceerder dan die van mysql) bepaald eerst voor zijn kunnen het beste queryplan, maar er geschat wordt dat het langer dan X seconden executietijd gaat duren schakelt ie een (nog) geavanceerdere planner in die gaat proberen het beste queryplan op te zoeken.

Als de planner dan 5 seconden bezig is om een executieplan van 15 vs 60 seconden te bedenken, ben je daar alleen maar dankbaar voor
Maar als het 5 seconden duurt om een plan te bedenken dat 0.1 ipv 0.5 seconden duurde... Dat heeft alleen zin bij prepared statements, daar zou het wel leuk zijn (hoewel dergelijke simpele queries niet zo gauw fout ingeschat worden door de "simpele" planner).

[ACM]

Creepy schreef op 16 juni 2004 @ 14:05:
Dus geen indexed search op (col2, col1) of (col2) i.v.m. leftmost prefix!
Of begrijp ik je nu verkeerd?

Je kan niet zoeken op (col2) of (col2, col3), maar wel op (col1, col2) of (col2, col1). Zo lang ide col1 er maar bij zit, probeer het maar

code:

mysql> explain select * from t where k1 = 1 and k2 = 2;
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| table | type | possible_keys | key     | key_len | ref         | rows | Extra                    |
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| t     | ref  | t_k1_k2       | t_k1_k2 |      10 | const,const |    9 | Using where; Using index |
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t where k2 = 2 and k1 = 1;
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| table | type | possible_keys | key     | key_len | ref         | rows | Extra                    |
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| t     | ref  | t_k1_k2       | t_k1_k2 |      10 | const,const |    9 | Using where; Using index |
+-------+------+---------------+---------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

[ Voor 64% gewijzigd door ACM op 16-06-2004 14:12 ]

[Creepy]

ACM schreef op 16 juni 2004 @ 14:09:
[...]

Je kan niet zoeken op (col2) of (col2, col3), maar wel op (col1, col2) of (col2, col1). Zo lang ide col1 er maar bij zit, probeer het maar

Ah, duidelijk

[jochemd]

Het zou natuurlijk helpen als je vertelde wat voor index (R tree, B(+) tree, bitmap, hash, GiST etc.) in welk dbms het betreft

[Verwijderd]

iedereen bedankt, ik heb weer wat bijgeleerd

ahv. execution plans is het mij gelukt om afwegingen te maken tussen verschillende indexes

jochemd: het gaat over DB2 en gewone, standaard BTree-indexes (niet geclusterd)

[ Voor 6% gewijzigd door Verwijderd op 19-06-2004 17:58 ]