Mysql Traag door order by Desc

[Verwijderd]

Ik heb een producten Database met 2.5 miljoen producten. Als ik daar de laatste producten uit een bepaald aantal categorieën wil laten zien. Dan lukt dat me niet op een snelle manier.

Er zit een Primary Key index op ID, en een index op Category_id. Ook heb ik beide velden al in 1 index gezet. Dit mocht echter ook niet baten.

Aan de hardware kan het ook niet liggen, we draaien op een HP proliant met 18GB ram en 2x Xeon Quad

Dit zijn de SQL commando's die ik uitvoer.

Ik ben mezelf hier al een aantal dagen op stuk aan het bijten. Maar kan tot nu toe nog geen oplossing vinden.

Wellicht heeft iemand een goede tip!

0.8892s deze query is traag

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159'))
ORDER BY `products`.`id` DESC LIMIT 25

0.0005s met 1 categorie is hij snel

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in ('99'))
ORDER BY `products`.`id` DESC  LIMIT 25

0.9983s met 2 categorieën wordt hij supertraag

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (99, 100))
ORDER BY `products`.`id` DESC  LIMIT 25

0.0013s met ASC is hij snel

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159'))
ORDER BY `products`.`id` ASC LIMIT 25

Explain

code:

| id | select_type | table    | type  | possible_keys | key     | key_len | ref | rows | Extra       |
|  1 | SIMPLE      | products | index | category      | PRIMARY | 4       | NULL |  286 | Using where |

[ Voor 36% gewijzigd door RobIII op 02-01-2012 16:12 . Reden: Code tags toegevoegd ]

[mjax]

Hoe zien je indexen eruit?

[RobIII]

Wil je voortaan als je code post code tags gebruiken a.u.b.? Dat maakt het geheel een stuk leesbaarder. Ik heb 't nu voor je gedaan maar let daar voortaan even op

[borft]

is de explain bij alle queries hetzelfde?

[ Voor 155% gewijzigd door borft op 02-01-2012 16:15 ]

[BKJ]

Als ik zo er naar kijk dan zie ik bij de trage query geen quotes en bij de snelle wel om de categorieën heen. Is het wel een int of toch stiekem een varchar?

[Erik Jan]

Je indexen zijn ascending. Wrap de query in een subquery en sorteer daarna.

[mjax]

Erik Jan schreef op maandag 02 januari 2012 @ 16:16:
Je indexen zijn ascending. Wrap de query in een subquery en sorteer daarna.

Of je maakt een extra descending index erbij.

[Erik Jan]

mjax schreef op maandag 02 januari 2012 @ 16:21:
Of je maakt een extra descending index erbij.

Dat kan MySQL (nog) niet

[NovaDesu]

mjax schreef op maandag 02 januari 2012 @ 16:21:
[...]


Of je maakt een extra descending index erbij.

Bestaat dit? De index is in zowel ascending en descending af te lopen omdat de leafs een doubly linked list is...

[TheNephilim]

Helpt een index met `category_id` & `product_id` niet? (ook in deze volgorde, dus eerst category_id dan product_id)

[OnTracK]

TheNephilim schreef op maandag 02 januari 2012 @ 17:07:
Helpt een index met `category_id` & `product_id` niet? (ook in deze volgorde, dus eerst category_id dan product_id)

Dat denk ik ook, want hij lijkt de category_id index in je EXPLAIN niet te gebruiken. Anders misschien eens die index proberen te forceren (heel voorzichtig mee zijn, want voor je het weet is de situatie qua snelheid bij bepaalde aantallen omgekeerd)

[Verwijderd]

BKJ schreef op maandag 02 januari 2012 @ 16:16:
Als ik zo er naar kijk dan zie ik bij de trage query geen quotes en bij de snelle wel om de categorieën heen. Is het wel een int of toch stiekem een varchar?

De quotes worden door Zend Framework automatisch in de query gezet. Als Vreemde sleutel heb ik natuurlijk een integer gebruikt ivm de snelheid.

RobIII schreef op maandag 02 januari 2012 @ 16:12:
Wil je voortaan als je code post code tags gebruiken a.u.b.? Dat maakt het geheel een stuk leesbaarder. Ik heb 't nu voor je gedaan maar let daar voortaan even op

Bedankt!

borft schreef op maandag 02 januari 2012 @ 16:14:
is de explain bij alle queries hetzelfde?

Jep

mjax schreef op maandag 02 januari 2012 @ 16:21:
[...]Of je maakt een extra descending index erbij.

Desc indexen bestaan nog niet in mysql.

OnTracK schreef op maandag 02 januari 2012 @ 17:15:
[...]
Dat denk ik ook, want hij lijkt de category_id index in je EXPLAIN niet te gebruiken. Anders misschien eens die index proberen te forceren (heel voorzichtig mee zijn, want voor je het weet is de situatie qua snelheid bij bepaalde aantallen omgekeerd)

Ik ga proberen de index te forceren, hier kom ik zo op terug!

Tot dusver bedankt voor de snelle recties!

[Olaf van der Spek]

Ik heb ook altijd ruzie met dit soort queries. Volgens mij kan MySQL dit niet fatsoenlijk.

Erik Jan schreef op maandag 02 januari 2012 @ 16:27:
[...]
Dat kan MySQL (nog) niet

Welke software wel? Een descending index is toch onzin?

[ Voor 53% gewijzigd door Olaf van der Spek op 02-01-2012 19:00 ]

[GlowMouse]

Wat je kunt doen is een FORCE INDEX(id) (of PRIMARY ipv id). Hij doorloopt de tabel dan in aflopende id-volgorde net zolang dat hij 25 records heeft gevonden die aan je WHERE voldoen. Afhankelijk van wat er in je WHERE staat, kan dat heel snel of heel langzaam gaan. Een betere oplossing als er veel producten zijn in de genoemde categorieën in je WHERE staan, is er niet in MySQL.

[kluyze]

Vreemde hersenkronkel:
Wat gebeurt er als je de id's in je where omdraait?

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (100, 99))
ORDER BY `products`.`id` DESC  LIMIT 25

[RobIII]

Olaf van der Spek schreef op maandag 02 januari 2012 @ 18:55:
Welke software wel?

In ieder geval SQL Server 2000 en hoger:
http://www.mssqltips.com/...ding-vs-descending-order/
http://www.sqlmag.com/article/sql-server/descending-indexes
http://stackoverflow.com/...oes-it-make#answer-743870

Evenals PostgreSQL en zo snel ik zie Oracle ook. MySQL vreet 't wel gewoon wanneer je een index Desc aanmaakt maar negeert 't vooralsnog.

quote: http://dev.mysql.com/doc/refman/5.6/en/create-index.html

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

[ Voor 54% gewijzigd door RobIII op 02-01-2012 21:14 ]

[GlowMouse]

kluyze schreef op maandag 02 januari 2012 @ 20:58:
Vreemde hersenkronkel:
Wat gebeurt er als je de id's in je where omdraait?

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (100, 99))
ORDER BY `products`.`id` DESC  LIMIT 25

Dan gebeurt er precies hetzelfde, met gokken kom je niet verder.

RobIII schreef op maandag 02 januari 2012 @ 21:07:
http://www.mssqltips.com/...ding-vs-descending-order/
http://www.sqlmag.com/article/sql-server/descending-indexes

Hierin staat:

Summary
As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.

Wat ook logisch is als je weet hoe indices werken. De geplaatste opmerking over descending indices is dus het verhaal van de klok en de klepel.

[RobIII]

GlowMouse schreef op maandag 02 januari 2012 @ 21:25:
De geplaatste opmerking over descending indices is dus het verhaal van de klok en de klepel.

Ik gaf alleen maar aan dat 't wel degelijk kan/bestaat De artikelen lijken me verder meer dan voldoende informatie te bevatten om klok/klepel verhalen uit de wereld te helpen

[ Voor 18% gewijzigd door RobIII op 02-01-2012 21:29 ]

[Voutloos]

Laat nou aub de explain van alle queries zien, want deze zal niet steeds hetzelfde zijn, al was het maar omdat de waarbij bij aantal rows zal verschillen.

En in welke volgorde heb je de samengestelde index getest? En welke engine gebruik je? Indien geen innodb: Stap over naar innodb indien sneller. Indien niet sneller: stap alsnog over.

Als het aantal categorieën in zo'n query binnen de perken blijft, zou je kunnen proberen om een UNION er van te maken.

Olaf van der Spek schreef op maandag 02 januari 2012 @ 18:55:
Welke software wel? Een descending index is toch onzin?

Elk dbms ondersteunt het.

[vieze hack]
Alleen moet je bij mysql het zelf doen; Introduceer een kolom welke descending waardes bevat. Aka sla '$maxWaardeVanDatDatatype - id' op in dergelijke kolom.
[/vieze hack]

Uiteraard moet je eerst andere oplossingen evalueren tot je tot deze vieze hack overgaat.

[Verwijderd]

kluyze schreef op maandag 02 januari 2012 @ 20:58:
Vreemde hersenkronkel:
Wat gebeurt er als je de id's in je where omdraait?

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (100, 99))
ORDER BY `products`.`id` DESC  LIMIT 25

Dit was inderdaad een leuke geweest, als het had gewerkt. Helaas geen winst door de Id's in aflopende volgorde op te nemen in de array.

[Verwijderd]

Laat nou aub de explain van alle queries zien, want deze zal niet steeds hetzelfde zijn, al was het maar omdat de waarbij bij aantal rows zal verschillen.

Bijgaand de explains van de 4 queries.

0.8892s deze query is traag

code:

explain SELECT sql_no_cache  id
FROM products 
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159')) 
ORDER BY `products`.`id` DESC LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', 'category', 'PRIMARY', '4', '', 228, 'Using where'

0.0005s met 1 categorie is hij snel

code:

SELECT sql_no_cache  id 
FROM products 
WHERE (products.category_id in ('99')) 
ORDER BY `products`.`id` DESC  LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'ref', 'category', 'category', '4', 'const', 30858, 'Using where; Using index'

0.9983s met 2 categorieën wordt hij supertraag

code:

explain SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (99, 100))
ORDER BY `products`.`id` DESC  LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', 'category', 'PRIMARY', '4', '', 1395, 'Using where'

0.0013s met ASC is hij snel

code:

explain SELECT sql_no_cache  id
FROM products 
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159')) 
ORDER BY `products`.`id` ASC LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', 'category', 'PRIMARY', '4', '', 228, 'Using where'

En in welke volgorde heb je de samengestelde index getest? En welke engine gebruik je? Indien geen innodb: Stap over naar innodb indien sneller. Indien niet sneller: stap alsnog over.

Ik werk al op Innodb, wat bedoel je met volgorde van de samengestelde query?

Als het aantal categorieën in zo'n query binnen de perken blijft, zou je kunnen proberen om een UNION er van te maken.

die union oplossing moet ik even onderzoeken, hoe zal dit werken?

[vieze hack]
Alleen moet je bij mysql het zelf doen; Introduceer een kolom welke descending waardes bevat. Aka sla '$maxWaardeVanDatDatatype - id' op in dergelijke kolom.
\[/vieze hack]

Deze heb ik getest. door de max van het Int veld toe te voegen werd de query niet sneller.

[ Voor 60% gewijzigd door Verwijderd op 02-01-2012 22:10 ]

[D-Raven]

Zomaar een stomme gedachte, maar gebaseerd op de query die je in de TS neerzette: Welk type heeft de category_id kolom? Want als dat een int is, en je geeft je in je in-query de params mee als varchars, dan zou dat wel eens de reden van je "trage" query kunnen zijn. Vanwege conversie e.d. die hij dan gaat doen.

[RobIII]

D-Raven schreef op maandag 02 januari 2012 @ 22:07:
Vanwege conversie e.d. die hij dan gaat doen.

Nou heeft MySQL zo z'n nukken maar ik mag toch hopen dat de query planner slim genoeg is om die conversie eenmalig voor 't daadwerkelijk uitvoeren van 't query plan te doen

[ Voor 3% gewijzigd door RobIII op 02-01-2012 22:10 ]

[Verwijderd]

D-Raven schreef op maandag 02 januari 2012 @ 22:07:
Zomaar een stomme gedachte, maar gebaseerd op de query die je in de TS neerzette: Welk type heeft de category_id kolom? Want als dat een int is, en je geeft je in je in-query de params mee als varchars, dan zou dat wel eens de reden van je "trage" query kunnen zijn. Vanwege conversie e.d. die hij dan gaat doen.

De quotes worden automatisch door Zend Framework geplaatst. Ik heb het zojuist getest, en dit mocht niet baten.

Helaas

[pedorus]

UNION wordt hier ook aangeraden. Ik vrees dat er in die 5 jaar nog niet genoeg veranderd is

[Voutloos]

Verwijderd schreef op maandag 02 januari 2012 @ 21:50:
...wat bedoel je met volgorde van de samengestelde query?

Index. Een index op A,B werkt anders dan B,A. Je zal dus beide versies willen testen. Overigens slaat innodb altijd al de PK ook in de index erbij op, dus in dit geval zal het geen uitkomst bieden.

die union oplossing moet ik even onderzoeken, hoe zal dit werken?

Een union tussen allemaal subqueries (met order by en limit) binnen 1 categorie, en vervolgens ook een order by + limit over de hele bups.

Deze heb ik getest. door de max van het Int veld toe te voegen werd de query niet sneller.

Een sommetje in je query helpt uiteraard niet, want de uitkomst van die som staat niet in je index. Ik had het expres over een extra kolom.

[ Voor 3% gewijzigd door Voutloos op 02-01-2012 22:18 ]

[D-Raven]

RobIII schreef op maandag 02 januari 2012 @ 22:10:
[...]

Nou heeft MySQL zo z'n nukken maar ik mag toch hopen dat de query planner slim genoeg is om die conversie eenmalig voor 't daadwerkelijk uitvoeren van 't query plan te doen

Ik heb geen idee hoe die in MySQL zn ding doet Maar ik weet dat bij MSSQL hij op de volledige table een conversie uit moet voeren, om daar vervolgens op te gaan filteren.
Dit specifieke issue heeft al eens voor redelijke snelheids winsten kunnen zorgen in projecten waarin ik gewerkt heb.

[F.West98]

Verwijderd schreef op maandag 02 januari 2012 @ 21:50:
[...]


Bijgaand de explains van de 4 queries.

0.8892s deze query is traag

code:

explain SELECT sql_no_cache  id
FROM products 
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159')) 
ORDER BY `products`.`id` DESC LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', 'category', 'PRIMARY', '4', '', 228, 'Using where'

0.0005s met 1 categorie is hij snel

code:

SELECT sql_no_cache  id 
FROM products 
WHERE (products.category_id in ('99')) 
ORDER BY `products`.`id` DESC  LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'ref', 'category', 'category', '4', 'const', 30858, 'Using where; Using index'

0.9983s met 2 categorieën wordt hij supertraag

code:

explain SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in (99, 100))
ORDER BY `products`.`id` DESC  LIMIT 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', 'category', 'PRIMARY', '4', '', 1395, 'Using where'

Ik zie hier dat je geen single quotes meer gebruikt?
Wat doet ie MET single quotes?

SQL:

SELECT sql_no_cache  id
FROM products
WHERE (products.category_id in ('99', '100'))
ORDER BY `products`.`id` DESC  LIMIT 25

[RobIII]

D-Raven schreef op maandag 02 januari 2012 @ 22:25:
Ik heb geen idee hoe die in MySQL zn ding doet Maar ik weet dat bij MSSQL hij op de volledige table een conversie uit moet voeren, om daar vervolgens op te gaan filteren.
Dit specifieke issue heeft al eens voor redelijke snelheids winsten kunnen zorgen in projecten waarin ik gewerkt heb.

offtopic:
Ik heb (seriously, not being sarcastic here ) een déjà vu. Deze discussie heb ik onlangs nog een keer eerder ergens gevoerd. Ik méén dat de uitkomst is/was dat er een paar zeer specifieke edge-cases zijn waarin dit voor kan komen maar anders dan dat weet ik 't niet meer. Ik weet in ieder geval dat de query planner/optimizer van MSSQL een van de beste is en ik heb erg veel moeite me voor te stellen dat 'ie voor elk record een cast gaat uitvoeren naar het type uit de query i.p.v. andersom. Het is natuurlijk andere koek bij UDF's en dat soort ongein.

[ Voor 3% gewijzigd door RobIII op 02-01-2012 22:32 ]

[D-Raven]

RobIII schreef op maandag 02 januari 2012 @ 22:29:
[...]

offtopic:
Ik heb (seriously, not being sarcastic here ) een déjà vu. Deze discussie heb ik onlangs nog een keer eerder ergens gevoerd. Ik méén dat de uitkomst is/was dat er een paar zeer specifieke edge-cases zijn waarin dit voor kan komen maar anders dan dat weet ik 't niet meer. Ik weet in ieder geval dat de query planner/optimizer van MSSQL een van de beste is en ik heb erg veel moeite me voor te stellen dat 'ie voor elk record een cast gaat uitvoeren naar het type uit de query i.p.v. andersom. Het is natuurlijk andere koek bij UDF's en dat soort ongein.

Deze issue ben ik 2 jaar geleden een paar keer tegen gekomen. Dus zou goed kunnen dat dit in de nieuwste versie van MSSQL gefixed/veranderd/verbeterd is. Ik ben er alleen sindsdien wat alerter op vandaar.
Maar het zijn inderdaad edge cases want ik meen me te herinneren dat het voornamelijk bij casting naar nvarchar voor problemen zorgde, en bij varchar dan weer niet/verwaarloosbaar. Met andere woorden conversie tussen unicode en non unicode data types zorgde voor problemen.

[cariolive23]

D-Raven schreef op maandag 02 januari 2012 @ 22:07:
Zomaar een stomme gedachte, maar gebaseerd op de query die je in de TS neerzette: Welk type heeft de category_id kolom? Want als dat een int is, en je geeft je in je in-query de params mee als varchars, dan zou dat wel eens de reden van je "trage" query kunnen zijn. Vanwege conversie e.d. die hij dan gaat doen.

Er staan in de query geen varchars en die quotes maken van een integer echt geen varchar. Dan zou iedere date ook een varchar zijn, daar zijn quotes namelijk verplicht. Wanneer er expliciet een CAST wordt gebruikt, dan wordt er gecast, in andere gevallen wordt gecheckt of de input overeenkomt met het datatype van de desbetreffende kolom in de tabel.

MySQL doet overigens niet heel erg zijn best om datatypes correct te verwerken, al kan het zijn dat je dit met een andere sql_mode kunt verbeteren. Maar dat maakt voor de quotes niet uit, een integer (of decimal) mag je best tussen quotes zetten, dat verandert helemaal niets aan het datatype.

[D-Raven]

cariolive23 schreef op maandag 02 januari 2012 @ 22:39:
[...]

Er staan in de query geen varchars en die quotes maken van een integer echt geen varchar. Dan zou iedere date ook een varchar zijn, daar zijn quotes namelijk verplicht. Wanneer er expliciet een CAST wordt gebruikt, dan wordt er gecast, in andere gevallen wordt gecheckt of de input overeenkomt met het datatype van de desbetreffende kolom in de tabel.

In mssql gebeurd dat ook.(iig met dates zeker) Daarom moet je dates altijd naar bekend formats casten (106 / 110 oid zo even uit mn hoofd), anders krijg je onverwachte dingen met sorteren en filteren. Maargoed Dates zijn zowiezo een verhaal apart.

Als je in MSSQL een parameters waarde tussen quotes zet, dan interpreteert hij deze als een varchar. Dat optimizer dit later omzet naar het datatype van de kolom of vice versa doet daar niks vanaf.
Maargoed als MySQL dit anders doet. Mooi

[JaQ]

Ik weet vrij weinig van mysql, maar ietsje meer van Oracle. Als een developer naar mij toe zou komen met dit probleem in een Oracle database zou ik de volgende twee zaken onderzoeken:

• impliciete conversie van varchar naar integer (of wat voor numerieke waarde je ook hebt), een trace met event 10046 zou dat aantonen. mysqld --debug kan je misschien helpen?
• statistieken op de tabel en de kolommen en de correctheid daarvan. De data verdeling van categorieën zou wel eens skew kunnen zijn. Echter ik weet niet of mysql zoiets kent als een histogram? (oftewel, op het juiste moment een full table scan over een index kiezen).

[GlowMouse]

RobIII schreef op maandag 02 januari 2012 @ 22:29:
[...]

offtopic:
Ik heb (seriously, not being sarcastic here ) een déjà vu. Deze discussie heb ik onlangs nog een keer eerder ergens gevoerd.

Dat was hier: RobIII in "Ik heb een MySQL error waar ik maar niet uitkom"

JaQ schreef op maandag 02 januari 2012 @ 23:09:
• statistieken op de tabel en de kolommen en de correctheid daarvan. De data verdeling van categorieën zou wel eens skew kunnen zijn. Echter ik weet niet of mysql zoiets kent als een histogram? (oftewel, op het juiste moment een full table scan over een index kiezen).

MySQL doet niet aan histograms.

[ Voor 46% gewijzigd door GlowMouse op 03-01-2012 00:16 ]

[ValHallASW]

RobIII schreef op maandag 02 januari 2012 @ 22:10:
Nou heeft MySQL zo z'n nukken maar ik mag toch hopen dat de query planner slim genoeg is om die conversie eenmalig voor 't daadwerkelijk uitvoeren van 't query plan te doen

Nee.

quote: http://meta.wikimedia.org/wiki/Toolserver/Database

Timestamps are not numbers, they are strings consisting of numeric characters. Make sure to enclose timestamps in quotes (for example, rc_timestamp > '200500000000'), or your your query will run much slower (up to 50 or more times slower).

En ja, dat merk je (zeker op tabellen als revision, met 25 mln (nlwiki) / 450 mln (enwiki) entries)...

[Verwijderd]

een grote stap vooruit, door use index(category) toe te voegen, is de parsetijd van de query van 1.8 seconden geminimaliseerd tot 0,3 seconden.

Echter blijft het probleem dat de ASC query, 0.02 seconden duurt, en de DESC query 0,32 seconden.

Voutloos schreef op maandag 02 januari 2012 @ 22:18:
[...]
Index. Een index op A,B werkt anders dan B,A. Je zal dus beide versies willen testen. Overigens slaat innodb altijd al de PK ook in de index erbij op, dus in dit geval zal het geen uitkomst bieden.

[...]
Een union tussen allemaal subqueries (met order by en limit) binnen 1 categorie, en vervolgens ook een order by + limit over de hele bups.

[...]
Een sommetje in je query helpt uiteraard niet, want de uitkomst van die som staat niet in je index. Ik had het expres over een extra kolom.

index id, category_id --> 4.2 sec

code:

SELECT sql_no_cache `products`.`id`, `products`.`name`, `products`.`price`, `products`.`image`, `products`.`category_id`
FROM `productstest` as products force index (category2)
WHERE (products.category_id in (99, 100, 101, 102, 103, 104, 105, 106, 117, 118, 119, 127, 140, 141, 159))
ORDER BY `products`.`id` desc limit 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'index', '', 'category2', '8', '', 25, 'Using where'

index id, category_id --> 0,3 sec sec

code:

explain SELECT sql_no_cache `products`.`id`, `products`.`name`, `products`.`price`, `products`.`image`, `products`.`category_id`
FROM `productstest` as products force index (category)
WHERE (products.category_id in (99, 100, 101, 102, 103, 104, 105, 106, 117, 118, 119, 127, 140, 141, 159))
ORDER BY `products`.`id` desc limit 25

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'range', 'category', 'category', '4', '', 222246, 'Using where; Using filesort'

extra colom maxint-id
deze optie heb ik nogmaals geprobeerd, met de force index (category) en force index for group nu (ordercol)

het resultaat is wel anders, 0.8 seconden.

code:

SELECT  SQL_NO_CACHE id
FROM productstest as products use index (category) use index for order by (ordercol)
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159'))
ORDER BY `products`.`ordercol` asc LIMIT 25;

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'range', 'category', 'category', '4', '', 222246, 'Using where; Using filesort'

union 0.7 sec
de union methode is een beetje omslachtig te bouwen. maar leid tot dusver nog niet naar performance winst t.o.v. de query met Use index (category) van 0,3 sec.
tevens vervalt het verschil in de ASC en DESC

code:

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 99)

union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 100)

union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 101)

union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 102)

union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 103)


union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 104)


union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 105)

union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 106)
union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 107)
union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 107)


ORDER BY `id` desc LIMIT 50,50;



id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'products', 'ref', 'category', 'category', '4', 'const', 30858, ''
2, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 5500, ''
3, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 10371, ''
4, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 36046, ''
5, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4120, ''
6, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1181, ''
7, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4355, ''
8, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4118, ''
9, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1009, ''
10, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1009, ''
, 'UNION RESULT', '<union1,2,3,4,5,6,7,8,9,10>', 'ALL', '', '', '', '', , 'Using filesort'

[GlowMouse]

Je unionmethode is niet correct, zie http://www.mysqlperforman...mization-query-profiling/

En ik mis de SHOW CREATE TABLE van je tabellen.

[Voutloos]

Verwijderd schreef op dinsdag 03 januari 2012 @ 11:44:
extra colom maxint-id
deze optie heb ik nogmaals geprobeerd, met de force index (category) en force index for group nu (ordercol)

het resultaat is wel anders, 0.8 seconden.

code:

SELECT  SQL_NO_CACHE id
FROM productstest as products use index (category) use index for order by (ordercol)
WHERE (products.category_id in ('99', '100', '101', '102', '103', '104', '105', '106', '117', '118', '119', '127', '140', '141', '159'))
ORDER BY `products`.`ordercol` asc LIMIT 25;

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'products', 'range', 'category', 'category', '4', '', 222246, 'Using where; Using filesort'

Je hebt het niet goed gedaan, want het kan dan juist zonder filesort. Test eens met een index category_id,ordercol.

union 0.7 sec
de union methode is een beetje omslachtig te bouwen. maar leid tot dusver nog niet naar performance winst t.o.v. de query met Use index (category) van 0,3 sec.
tevens vervalt het verschil in de ASC en DESC

code:

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 99)

union
[...]
union

(SELECT   id, name, ordercol
FROM productstest as products use index (category)
WHERE products.category_id = 107)


ORDER BY `id` desc LIMIT 50,50;



id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'products', 'ref', 'category', 'category', '4', 'const', 30858, ''
2, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 5500, ''
3, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 10371, ''
4, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 36046, ''
5, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4120, ''
6, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1181, ''
7, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4355, ''
8, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 4118, ''
9, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1009, ''
10, 'UNION', 'products', 'ref', 'category', 'category', '4', 'const', 1009, ''
, 'UNION RESULT', '<union1,2,3,4,5,6,7,8,9,10>', 'ALL', '', '', '', '', , 'Using filesort'

Klopt ook niet, maar gelukkig zei ik al niet al dat je zowel in de subquery als over het totaal de order by moet doen.

[GlowMouse]

Voutloos schreef op dinsdag 03 januari 2012 @ 13:03:
[...]
Je hebt het niet goed gedaan, want het kan dan juist zonder filesort.

behalve voor de UNION

[Voutloos]

offtopic:
Ik reageer op 2 verschillende query ideeen he. Die Union gaat uiteraard het eindresultaat niet kunnen sorteren dmv een index. Beide ideeen had ik ook voldoende voorgekauwd, dus het is weer goed gesteld met het begrijpend lezen hier.

[GlowMouse]

Voutloos schreef op dinsdag 03 januari 2012 @ 13:15:

offtopic:
Ik reageer op 2 verschillende query ideeen he. Die Union gaat uiteraard het eindresultaat niet kunnen sorteren dmv een index. Beide ideeen had ik ook voldoende voorgekauwd, dus het is weer goed gesteld met het begrijpend lezen hier.

Dan klopt je opmerking ook niet. Met een index op category_id,ordercol kan hij de rijen bij de category_id's wel efficiënt opzoeken, maar niet sorteren omdat er meerdere categorieën zijn.
Een index op ordercol kan het wel zonder filesort, maar dan kunnen de categorieën weer niet efficiënt gevonden worden.

[Voutloos]

Hmz, daar heb je eigenlijk wel gelijk in. Dan moet het waarschijnlijk wel de union aanpak worden...

[GlowMouse]

Voutloos schreef op dinsdag 03 januari 2012 @ 13:23:
Hmz, daar heb je eigenlijk wel gelijk in.

Onlogisch is het wel omdat het niet moeilijk is om een aantal gesorteerde lijsten samen te voegen tot één grote gesorteerde lijst. Zelfs voor duizend categorieën mag dat geen onoverkomelijk geheugenprobleem opleveren. Ik kan het me voorstellen als er db servers zijn die dit wel kunnen.

[Olaf van der Spek]

GlowMouse schreef op dinsdag 03 januari 2012 @ 13:27:
Onlogisch is het wel omdat het niet moeilijk is om een aantal gesorteerde lijsten samen te voegen tot één grote gesorteerde lijst. Zelfs voor duizend categorieën mag dat geen onoverkomelijk geheugenprobleem opleveren. Ik kan het me voorstellen als er db servers zijn die dit wel kunnen.

Dat MySQL zo'n simpele query niet fatsoenlijk kan uitvoeren is eigenlijk al niet te begrijpen. Een simpele category index zou voldoende moeten zijn.

[GlowMouse]

Olaf van der Spek schreef op dinsdag 03 januari 2012 @ 13:57:
[...]

Dat MySQL zo'n simpele query niet fatsoenlijk kan uitvoeren is eigenlijk al niet te begrijpen. Een simpele category index zou voldoende moeten zijn.

Leg dan eens uit hoe je een sortering op product-id uit een BTREE-index op category kunt halen.

[Olaf van der Spek]

GlowMouse schreef op dinsdag 03 januari 2012 @ 14:10:
Leg dan eens uit hoe je een sortering op product-id uit een BTREE-index op category kunt halen.

Dat is niet nodig, met de category index verzamel je product IDs die je daarna sorteert. Voor dat sorteren heb je geen index nodig.

Het kan wel trouwens, als de entries met gelijke category zijn geordend op primary key ID.

[ Voor 18% gewijzigd door Olaf van der Spek op 03-01-2012 14:32 ]

[GlowMouse]

Olaf van der Spek schreef op dinsdag 03 januari 2012 @ 14:28:
[...]

Dat is niet nodig, met de category index verzamel je product IDs die je daarna sorteert. Voor dat sorteren heb je geen index nodig.

Dat is precies een van de strategieën die MySQL toepast en die te langzaam is voor TS.

Het kan wel trouwens, als de entries met gelijke category zijn geordend op primary key ID.

Klopt, daar ging mijn post van 13:27 over.

[Olaf van der Spek]

GlowMouse schreef op dinsdag 03 januari 2012 @ 14:42:
Dat is precies een van de strategieën die MySQL toepast en die te langzaam is voor TS.

Maar waarom is die strategie zo traag? Het sorteren van een lijst IDs mag toch niet zo lang duren?

[GlowMouse]

Olaf van der Spek schreef op dinsdag 03 januari 2012 @ 14:47:
[...]

Maar waarom is die strategie zo traag? Het sorteren van een lijst IDs mag toch niet zo lang duren?

Het kan zomaar om een miljoen records gaan die in de genoemde categorieën zitten. Hij selecteert ook de kolommen name en ordercol. Afhankelijk van het datatypes van deze velden gaat de sortering direct via een temptabel op de harddisk of eerst in het geheugen maar als de tabel boven een bepaalde grootte komt alsnog op de harddisk.

[Olaf van der Spek]

Je sorteert toch voordat je die andere velden leest? Een miljoen IDs sorteren lijkt me nog steeds goed te doen.

[ Voor 36% gewijzigd door Olaf van der Spek op 03-01-2012 15:16 ]

[GlowMouse]

Olaf van der Spek schreef op dinsdag 03 januari 2012 @ 15:06:
Je sorteert toch voordat je die andere velden leest?

Standaard niet, hoewel er wel een query met een subquery voor te verzinnen is die dat wel doet.

[Olaf van der Spek]

GlowMouse schreef op dinsdag 03 januari 2012 @ 15:33:
Standaard niet, hoewel er wel een query met een subquery voor te verzinnen is die dat wel doet.

Waarom niet? Het is toch zonde die andere velden te lezen om ze daarna weg te gooien?

[GlowMouse]

Olaf van der Spek schreef op dinsdag 03 januari 2012 @ 15:37:
[...]

Waarom niet? Het is toch zonde die andere velden te lezen om ze daarna weg te gooien?

Goede vraag. MySQL 5.6 doet het al een stuk beter door bij ORDER BY ... LIMIT 50 tijdens het sorteren ten hoogste 50 records te onthouden.

[Olaf van der Spek]

GlowMouse schreef op dinsdag 03 januari 2012 @ 15:41:
[...]

Goede vraag. MySQL 5.6 doet het al een stuk beter door bij ORDER BY ... LIMIT 50 tijdens het sorteren ten hoogste 50 records te onthouden.

Is alleen nuttig als je de eerste of laatste 50 wil. Niet zo heel erg nuttig dus.

[wackmaniac]

Ik heb het tussendoor nog niet gezien, maar in je productieomgeving laat je die no_cache wel weg toch? Beter 1 keer traag dan elke keer traag

Even een brainfart:
Heb je al geprobeerd de IN om te zetten naar een conjunctie?

MySQL wil nog wel eens rare strapatsen uithalen in het kiezen van indices.

[GlowMouse]

wackmaniac schreef op dinsdag 03 januari 2012 @ 20:30:
Ik heb het tussendoor nog niet gezien, maar in je productieomgeving laat je die no_cache wel weg toch? Beter 1 keer traag dan elke keer traag

Bij een goede productieserver staat querycache uit en maakt het geen verschil. Op querycache zit een global mutex waardoor alle queries geblokkeerd worden op het moment dat er door een update/insert-query de querycache doorlopen moet worden op te verwijderen resultsets.

[wackmaniac]

Dat ligt er maar aan wat je wilt doen; als jij 2.5 mln producten hebt die je 1 keer in de week bijwerkt zou ik een query cache in ieder geval overwegen. Als je beduidend meer reads dan writes hebt kan het een boel snelheid opleveren. Dat is in ieder geval wat ik uit de MySQL-documentatie en dit artikel begrijp.

[GlowMouse]

wackmaniac schreef op dinsdag 03 januari 2012 @ 23:07:
Dat ligt er maar aan wat je wilt doen; als jij 2.5 mln producten hebt die je 1 keer in de week bijwerkt zou ik een query cache in ieder geval overwegen. Als je beduidend meer reads dan writes hebt kan het een boel snelheid opleveren. Dat is in ieder geval wat ik uit de MySQL-documentatie en dit artikel begrijp.

Query cache is een setting voor je server, niet voor een query. Voor zoiets stop je je resultaat in een aparte tabel.

[Gomez12]

wackmaniac schreef op dinsdag 03 januari 2012 @ 23:07:
Dat ligt er maar aan wat je wilt doen; als jij 2.5 mln producten hebt die je 1 keer in de week bijwerkt zou ik een query cache in ieder geval overwegen. Als je beduidend meer reads dan writes hebt kan het een boel snelheid opleveren. Dat is in ieder geval wat ik uit de MySQL-documentatie en dit artikel begrijp.

Querycache is enkel een poor man's cache.

Het is op zich niet verkeerd om het aan te hebben staan (imho) maar er actief op vertrouwen kan leiden tot ietwat onvoorspelbare resultaten.
Persoonlijk heb ik hem ook liever uit, liever ietwat trager maar voorspelbare query-tijden als "zo af en toe" een langzame query die normaal wel weer snel is. Die 2e categorie wens ik in ieder geval niet te debuggen