[MySQL] Index wordt niet gebruikt met !=

vrijdag 21 december 2007 17:42

Indexen worden gebruikt om een item snel te kunnen zoeken, als jij een kennelijk random item wilt wat aan een conditie voldoet (namelijk, het item is niet ###) zal MySQL daarom de index negeren, "random" een item pakken, kijken of dit item voldoet aan je conditie en zoniet verder zoeken.

Die index komt er dus simpelweg helemaal niet bij aan te pas door het type query wat je gebruikt. Enkel als je een specifiek item zoekt kan een index gebruikt worden, dit is inherent aan het principe index.

//edit @ hierboven: met die subquery zal voor de subquery zelf wel een index gebruikt worden, maar voor de gehele query niet. Heeft dus ook geen nut

[ Voor 13% gewijzigd door FragFrog op 21-12-2007 17:31 ]

[ Site ] [ twitch ] [ jijbuis ]

Acties:

JaQ

FireFoz schreef op vrijdag 21 december 2007 @ 16:59:
sorry, ik moet er ff aan toevoegen dat ik LIMIT 1 gebruik. ik wil namelijk 1 rij terug hebben waarbij het id niet 32,493,1923 (bijv) is.

dus het transport is het niet lijkt me, want hij stuurt maar 1 result terug

maar dan kan je dat toch niet op een andere manier selecteren? met <> wordt de index ook niet gebruikt

De optimizer doet precies wat je wilt, namelijk geen index gebruiken (immers, je selecteert meer dan 99% van de tabel). Een limit wordt pas toegepast nadat alle resultaten zijn gevonden.

Overigens is het niet zo dat een query altijd goed is, zodra deze een index gebruikt.

Egoist: A person of low taste, more interested in themselves than in me

vrijdag 21 december 2007 17:48

Acties:

FireFoz

Topicstarter

okay, dus deze query valt verder niet te optimaliseren? ik was er eigenlijk al bang voor, want je zegt dus idd eigenlijk alles behalve x x en x.

ik zou ook niet weten hoe ik extra criteria kan gebruiken, ik wil gewoon een rij pakken die nog niet gebruikt is in het script (deze hou ik in een array bij en plak ik steeds achter de query).

Leef lekker in het nu, er is niks anders

vrijdag 21 december 2007 17:59

Acties:

Gomez12

FireFoz schreef op vrijdag 21 december 2007 @ 17:48:
okay, dus deze query valt verder niet te optimaliseren? ik was er eigenlijk al bang voor, want je zegt dus idd eigenlijk alles behalve x x en x.

ik zou ook niet weten hoe ik extra criteria kan gebruiken, ik wil gewoon een rij pakken die nog niet gebruikt is in het script (deze hou ik in een array bij en plak ik steeds achter de query).

Definieer niet gebruikt in het script eens.

Want ik denk dat je rustig een extra tabelletje kan aanmaken met gebruikte id's, dan een where met een inner join. En ik denk dat je dan wel indexen gebruikt.

Maar als het gewoon om eenmalige te gebruiken items zijn dan zou ik in php een random id genereren, hiermee een query uitvoeren om alleen dit id op tehalen. Dan kan je in php wel randomizen op dat het niet in jouw array mag zitten.

vrijdag 21 december 2007 18:45

Acties:

vrijdag 21 december 2007 19:15

Only The Best is Good Enough.

misschien dat je eerst kan beschrijven wat je wilt bereiken.
wil je kijken of er een ander record is dat niet aan de ID voldoet?
dan kan je ook gebruik maken van een soort dummy tabel:

code:

1	select 1 from dual where exists (select id from tabel where id <> 12345)

deze zou moeten ophouden na de eerste fetch van een record.

anders zou je misschien ook nog kunnen proberen om de max of min op te halen

code:

select *
from   tabel t1 
where t1.id in (select min(t2.id)
                from tabel t2 
                where t2.id <> 12345
               )

wellicht dat dit wel gebruik maakt van indexes

[ Voor 30% gewijzigd door Goodielover op 21-12-2007 18:50 ]

Acties:

vrijdag 21 december 2007 22:21

Goodielover schreef op vrijdag 21 december 2007 @ 18:45:
code:
1
2
3
4
5
6
select *
from   tabel t1 
where t1.id in (select min(t2.id)
                from tabel t2 
                where t2.id <> 12345
               )
wellicht dat dit wel gebruik maakt van indexes

De hoofdquery zal nu gebruik maken van de index, de subquery niet. Aangezien je nu twee queries zit te doen zal die nog steeds relatief traag zijn. Als je echt per se een index wilt gebruiken zul je een specifiek ID moeten genereren:

SQL:

SELECT  `field`
FROM    `tabel`
WHERE   `id`   = FLOOR(RAND() * 100)
  AND   `used` = 0;

(En ja, in plaats van 100 moet je technisch gezien een SELECT MAX(`id`) plaatsen als random multiplier, maar de query is zo al te zot voor woorden

)

Helaas kun je niet weten welke ID's used = 0 hebben zonder een subquery dus zul je 'm net zo lang moeten herhalen tot je wel een goed ID krijgt. Ergo, enorm inefficient.

Zoals ik al zei, voor dit type query is het niet mogelijk de primary key index te gebruiken omdat dat simpelweg niet is waar indici voor zijn! Het is dan absoluut zinloos om het toch te proberen omdat elke andere oplossing die wel die index gebruikt trager zal zijn!

//edit
Lees anders dit eens, en dan specifiek de eerste regel:

Indexes are used to find rows with specific column values quickly.

//edit2
Overigens zou je wel een index op je conditie-colom kunnen zetten, maar aangezien je index cardinaliteit belachelijk laag zal zijn (verreweg de meeste colommen zitten op 1 index group) is ook het nut daarvan minimaal.

[ Voor 20% gewijzigd door FragFrog op 21-12-2007 19:25 ]

[ Site ] [ twitch ] [ jijbuis ]

Acties:

MSalters

FireFoz schreef op vrijdag 21 december 2007 @ 17:48:
okay, dus deze query valt verder niet te optimaliseren? ik was er eigenlijk al bang voor, want je zegt dus idd eigenlijk alles behalve x x en x.

ik zou ook niet weten hoe ik extra criteria kan gebruiken, ik wil gewoon een rij pakken die nog niet gebruikt is in het script (deze hou ik in een array bij en plak ik steeds achter de query).

Je bedoelt dus dat je niet één != gebruikt, maar een hele set? Tja, tegen de tijd dat je 100.000 checks doet wordt het wel langzaam ja.

Man hopes. Genius creates. Ralph Waldo Emerson
Never worry about theory as long as the machinery does what it's supposed to do. R. A. Heinlein

zaterdag 22 december 2007 16:02

Acties:

zaterdag 22 december 2007 17:37

Only The Best is Good Enough.

FragFrog schreef op vrijdag 21 december 2007 @ 19:15:
[...]

De hoofdquery zal nu gebruik maken van de index, de subquery niet. Aangezien je nu twee queries zit te doen zal die nog steeds relatief traag zijn. Als je echt per se een index wilt gebruiken zul je een specifiek ID moeten genereren:

[...]

De query van mij zal bloed snel zijn. Voor beide wordt de index gebruikt.
In de subquery wordt de min uit de index opgehaald, in de hoofdquery op ID de tabel.
Heb je 'm uitgeprobeerd...?

Acties:

zaterdag 22 december 2007 21:36

Met ORDER BY id ASC en LIMIT 1 doe je exact hetzelfde, maar dan in 1 supereenvoudige query.

Sim-pel.

{signature}

Acties:

ValHallASW

Als het je gaat om k rijen en je hebt n rijen die je wilt negeren, dan selecteer je er toch gewoon (k+n) die je vervolgens filtert? Geen extra tabel nodig (nuja, eentje in het geheugen)

dat geeft iets als

SQL:

SELECT * FROM
(SELECT * FROM tabel LIMIT 2) AS t
WHERE t.ID != 1292
LIMIT 1

even toegepast op de page table van enwiki (14842179 rows) geeft dat bijvoorbeeld:

SQL:

1	select * from (select * from page limit 2) as t where t.page_id!=5 limit 1;

ook retesnel

en het toont maar weer aan dat je doodstaren op een index niet zinvol is. houd er wel rekening mee dat het niet random is, maar random en databases zijn sowieso slecht verenigbaar

edit: of toch wel een index:

SQL:

1	select * from (select * from page limit 2) as t where t.page_id!=10 limit 1;

levert op de pagina AlgeriA, met page_id 5, maar

SQL:

1	select * from (select page_id, page_namespace, page_title from page limit 2) as t where t.page_id!=10 limit 1;

levert op pagina !, met page_id 5878274... daar wordt namelijk opeens wél een index gebruikt!

edit2: wacht even hoor, maar

mysql> select * from page where page_id != 1 limit 1;
(...)
1 row in set (0.00 sec)
mysql> explain select * from page where page_id != 1 limit 1;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | page  | range | PRIMARY       | PRIMARY | 4       | NULL | 3297241 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

is mijn sql-server belachelijk snel of is hier iets anders aan de hand?

[ Voor 66% gewijzigd door ValHallASW op 22-12-2007 22:09 . Reden: +stukje over enwiki met index, + != is wél snel? ]

zondag 23 december 2007 11:56

Acties:

zondag 23 december 2007 13:29

Nofi, maar wat een compleet onzinnige queries ValHallASW. In geen enkele query is de derived table nodig, laat staan nuttig.

Ik verbaas me echt over hoe veel mensen moeilijk lopen te doen in dit topic. Een enkele query met limit welke geen filesort oid nodig heeft is gewoon retesnel en subqueries zijn hier echt overbodig.

SPee schreef op vrijdag 21 december 2007 @ 17:25:Verder: Controleer de werking van de 'LIMIT 1'. Wacht deze totdat de gehele query klaar is en stuurt dan alleen de eerste record terug? (is volgens mij het geval) In dat geval haalt hij wél éérst de hele recordset op.

Ik was hier eerst niet op in gegaan, maar alsjeblieft, lees eerst zelf eens wat in het optimization hoofdstuk: http://dev.mysql.com/doc/refman/5.0/en/optimization.html
(geldt voor meerdere mensen in dit topic). Een kleine limit bij een eenvoudige query is juist optimaal.

Als je dit soort queries gewoon normaal opschrijft heb je hoogstens 1 eenvoudig indexje nodig. Dit soort queries zal nooit de bottleneck in je applicatie zijn, andere queries zijn meer aandacht waard. Optimaliseren is ook weten wat je moet aanpakken, dus bijvoorbeeld je tijd steken in die grote query met filesorts en temporary tables welke wel de potentie heeft om meer dan een paar triviale milliseconden te duren.

{signature}

Acties:

ValHallASW

Voutloos schreef op zondag 23 december 2007 @ 11:56:Ik verbaas me echt over hoe veel mensen moeilijk lopen te doen in dit topic. Een enkele query met limit welke geen filesort oid nodig heeft is gewoon retesnel en subqueries zijn hier echt overbodig.

Lees de topicstart nog eens. De TS beweert dat de 'simpele limit 1'-oplossing traag is... ja, gewoon

SQL:

1	select * from table where id != 1 limit 1;

draait bij mij ook retesnel, maar bij de TS blijkbaar niet:

select * from tabel where id != 1292 LIMIT 1;

dan duurt het kei lang en wordt de index niet gebruikt.

Ik vraag me wel ernstig af welke versie van MySQL de TS gebruikt, aangezien 4.1 dit soort dingen (volgens de manual) ook al kan.. hoewel de manual wel aangeeft dat deze strategie alleen bij ORDER BY gebeurt. Best apart als je toch een index kunt gebruiken...

Als (ja, als) de server daadwerkelijk alle rows naloopt, dan is de enige logische oplossing om het aantal rows zo klein mogelijk te maken. Het minimaal aantal rows dat je nodig hebt om 1 onbekende row te krijgen met n bekende rows is (n+1)... en dat is dus precies wat ik doe: je beperkt het aantal rijen dat je vergelijkt.

offtopic:
En eh, je verwijt mij dat ik een 'totaal onzinnige query' schrijf? Scroll up en lees de rest van het topic eens

[ Voor 10% gewijzigd door ValHallASW op 23-12-2007 13:31 ]

zondag 23 december 2007 13:39

Acties:

zondag 23 december 2007 15:49

Ik gaf ook commentaar op anderen.

Maar goed, dan doen we toch een stapje terug en stel ik alsnog de vragen welke gesteld hadden mogen worden voordat er rare queries verzonnen gingen worden:
TS: Welke mysql versie gebruik je, wat is de exact uitgevoerde query, wat is de tabel definitie en wat zegt explain van je query?

{signature}

Acties:

maandag 24 december 2007 10:32

Goodielover schreef op zaterdag 22 december 2007 @ 16:02:
De query van mij zal bloed snel zijn. Voor beide wordt de index gebruikt.
In de subquery wordt de min uit de index opgehaald, in de hoofdquery op ID de tabel.
Heb je 'm uitgeprobeerd...?

Heb je'm zelf wel uitgeprobeerd? Je hebt gelijk, ik heb me vergist, de subquery gebruikt inderdaad een index, het is de hoofdquery die er geen gebruikt

Ik heb hem hier nu ook getest op een tabel met 50.000 rows, jou variant met subquery doet er in totaal 5.0589 seconden over, zonder subquery is hij klaar in 0.0444 seconden. Scheelt maar een factor 100

Overigens wordt het plaatje wat minder absurd als je de queries wat slimmer opzet:

SQL:

select *
from   tabel t1 
where t1.id = (select min(t2.id)
                from tabel t2 
                where t2.id <> 12345
LIMIT 1
               )

Deze query gebruikt wel voor zowel de hoofd- als subquery een index, maar die doet er hier ~0.219 seconden over - ergo, nog steeds een factor ~5 langzamer dan een enkele query.

Ik sluit me dan ook volledig aan bij voutloos, als TS's query echt zo traag is moet'ie 'm hier eens volledig posten inclusief table definition, want een simpele select met een limit en een enkele conditie is voor zover ik weet toch echt de allersnelste oplossing.

[ Voor 20% gewijzigd door FragFrog op 23-12-2007 15:59 ]

[ Site ] [ twitch ] [ jijbuis ]

Acties:

maandag 24 december 2007 10:35

Only The Best is Good Enough.

FragFrog schreef op zondag 23 december 2007 @ 15:49:
[...]

Heb je'm zelf wel uitgeprobeerd? Je hebt gelijk, ik heb me vergist, de subquery gebruikt inderdaad een index, het is de hoofdquery die er geen gebruikt

Ik heb hem hier nu ook getest op een tabel met 50.000 rows, jou variant met subquery doet er in totaal 5.0589 seconden over, zonder subquery is hij klaar in 0.0444 seconden. Scheelt maar een factor 100

Overigens wordt het plaatje wat minder absurd als je de queries wat slimmer opzet:
SQL:
1
2
3
4
5
6
7
select *
from   tabel t1 
where t1.id = (select min(t2.id)
                from tabel t2 
                where t2.id <> 12345
LIMIT 1
               )
Deze query gebruikt wel voor zowel de hoofd- als subquery een index, maar die doet er hier ~0.219 seconden over - ergo, nog steeds een factor ~5 langzamer dan een enkele query.

Ik sluit me dan ook volledig aan bij voutloos, als TS's query echt zo traag is moet'ie 'm hier eens volledig posten inclusief table definition, want een simpele select met een limit en een enkele conditie is voor zover ik weet toch echt de allersnelste oplossing.

Ik zie dat je het met de limit 1 wel snel krijgt. Ik leidt hieruit af dat "id" blijkbaar geen ID is.
Normaal gesproken heb ik in mijn tabellen een uniek veld dat ID heet. dan is de limit 1 dus compleets zinloos.
het zal wel aan mijn gebrek aan het snappen jouw datamodel liggen.

Acties:

maandag 24 december 2007 10:52

Only The Best is Good Enough.

Voutloos schreef op zaterdag 22 december 2007 @ 17:37:
Met ORDER BY id ASC en LIMIT 1 doe je exact hetzelfde, maar dan in 1 supereenvoudige query.
Sim-pel.

Mee eens, functioneel hetzelfde en kortere query. Mijn "kromme" constructie was er ook alleen om het gebruik van de index te forceren.
Maar ik heb het vermoeden dat we weer eens veel te weinig info hebben gekregen over het daadwerkelijke datamodel en de indices die er op staan.

Acties:

maandag 24 december 2007 17:06

Goodielover schreef op maandag 24 december 2007 @ 10:32:
Normaal gesproken heb ik in mijn tabellen een uniek veld dat ID heet. dan is de limit 1 dus compleets zinloos.

'<>' is hetzelfde als de '!=' operator, dus juist meer rijen mogelijk.

{signature}

Acties:

MacWebber

ValHallASW schreef op zondag 23 december 2007 @ 13:29:
Ik vraag me wel ernstig af welke versie van MySQL de TS gebruikt, aangezien 4.1 dit soort dingen (volgens de manual) ook al kan.. hoewel de manual wel aangeeft dat deze strategie alleen bij ORDER BY gebeurt. Best apart als je toch een index kunt gebruiken...

Ik heb hier een nog oudere versie draaien, 3.23 om precies te zijn. Die doet over een vergelijkbare query, op een tabel met een slordige 26 miljoen records (met index) zo'n 0.0317 sec.

Ik ga gokken dat de TS nog wat info achterhoudt

maandag 24 december 2007 21:45

Acties: