[SQL] Index in combinatie met < teken

[TheNephilim]

Het is misschien een wat simpel vraagstuk, maar ik ga het toch eens vragen. Heb log-queries-not-using-indexes aangezet in onze mysqlsetup om te checken of ze er nog zijn.

Nou kom ik er een paar tegen, allen van hetzelfde type:

SQL:

DELETE FROM `sessions` WHERE `session_timestamp`<1275378301

en

SQL:

UPDATE
  (`health` AS `h`
  INNER JOIN
    `stable` AS `s`
  USING
    (`id`))
SET
  `h`.`coat` = CASE WHEN (`h`.`coat` > 0) THEN `h`.`coat`-1 END,
  `h`.`hooves` = CASE WHEN (`h`.`hooves` > 0) THEN `h`.`hooves`-1 END,
  `h`.`manure` = CASE WHEN (`h`.`manure` < 100) THEN `h`.`manure`+1 END

Met groter dan of kleiner dan teken. Heb vanalles geprobeerd (force index, etc). Nu is bekend dat er met < of > geen indexes gebruikt worden, maar daar moet toch een oplossing voor wezen. Vooral bij de session garbage controller (cronjob) vind ik het belangrijk om een index te kunnen gebruiken.

Iemand hier een oplossing voor?

[Janoz]

Bij de tweede kan ik me voorstellen dat daar neit een index gebruikt wordt van wege de case, maar bij de eerste zou ik verwachten dat er wel degelijk gebruik gemaakt zou worden van een index.

Welke DB gebruik je en wat zijn de definities van je tabellen?

[TheNephilim]

Bedankt voor je reactie! We beginnen dan maar even met de eerste query, overigens is het een MySQL 5.1.46 config (master-slave) dit is de master. Er hangen nog 2 slaves aan dus. Ik neem aan dat je onderstaand als de definities bedoeld

SQL:

CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `session_ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `session_timestamp` int(10) unsigned NOT NULL,
  `session_data` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`session_id`),
  KEY `session_timestamp` (`session_timestamp`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

Op deze en nog een tabel na bestaan alle tabellen overigens uit InnoDB tabellen.

[GlowMouse]

Het executieplan hangt bij InnoDB sterk af van de cardinaliteit van je indices. Maar de belangrijkste vraag: zijn deze queries langzaam en hoevaak draaien ze? En bij die tweede: hoeveel % van de rijen wordt aangepast?

[Facer]

In de create table van session geef je aan dat er een PK is van session_id en session_timestamp maar dat er geen index is op alleen de kolom session_timestamp. Hierdoor wordt geen index gebruikt.

GlowMouse: Mijn reactie klopt inderdaad niet. "Key" is een synoniem voor "Index" in MySQL voor compatibility redenen.

[ Voor 24% gewijzigd door Facer op 01-06-2010 12:08 . Reden: Aangepast na aanleiding van reactie van GlowMouse ]

[GlowMouse]

Facer schreef op dinsdag 01 juni 2010 @ 11:52:
In de create table van session geef je aan dat er een PK is van session_id en session_timestamp maar dat er geen index is op alleen de kolom session_timestamp. Hierdoor wordt geen index gebruikt.

Nee hoor, er is een PK session_id en een KEY session_timestamp.

[TheNephilim]

@ GlowMouse: De session cron draait 1 per minuut, die 2e draait 1x per 15 minuten. In beide gevallen moet ie een tablescan doen omdat er geen index bruikbaar is. Vooral bij die 2e is dat niet handig, aangezien er zeker 20% aangepast gaat worden (schatting). De queries zijn nog niet langzaam, omdat het nog niet online staat.

@ Facer: PK op session_id en KEY op session_timestamp inderdaad...

Verder zit ik me te bedenken dat het misschien kan liggen aan de HASH index die de MEMORY engine standaard gebruikt?

[cariolive23]

Bernardo schreef op dinsdag 01 juni 2010 @ 12:04:
Verder zit ik me te bedenken dat het misschien kan liggen aan de HASH index die de MEMORY engine standaard gebruikt?

Een hash is niet gesorteerd, er is dus niet met < of > vast te stellen of iets groter of kleiner is. Gebruik een btree, die is gesorteerd. MEMORY kent ook btree-indexen, dat is dus geen probleem.

[GlowMouse]

Ah ik las er overheen. Let even op dat varchar in memory als char werkt, zonde dus als je die te lang maakt. Zeker omdat je ook nog eens utf-8 gebruikt, scheelt dat flink.

Die tweede query kun je opsplitsen in drie losse die elk wel een index kunnen gebruiken, maar als 20% wijzigt, weet ik niet wat sneller is. Omdat het op de achtergrond draait, zou ik hem zo houden. Het effect is lastig te benchmarken omdat je indices in een eventueel nieuwe situatie bijgewerkt moeten worden en dat bij InnoDB niet direct gebeurt.

[Verwijderd]

Misschien is MongoDb iets voor je: http://www.mongodb.org/

MongoDB bridges the gap between key-value stores (which are fast and highly scalable) and traditional RDBMS systems (which provide rich queries and deep functionality).

MongoDB (from "humongous") is a scalable, high-performance, open source, document-oriented database. Written in C++, MongoDB features:

* Document-oriented storage »

JSON-style documents with dynamic schemas offer simplicity and power.
* Full Index Support »

Index on any attribute, just like you're used to.
* Replication & High Availability »

Mirror across LANs and WANs for scale and peace of mind.
* Auto-Sharding »

Scale horizontally without compromising functionality.
* Querying »

Rich, document-based queries.
* Fast In-Place Updates »

Atomic modifiers for contention-free performance.
* Map/Reduce »

Flexible aggregation and data processing.
* GridFS »

Store files of any size without complicating your stack.

[GlowMouse]

Verwijderd schreef op dinsdag 01 juni 2010 @ 12:19:
Misschien is MongoDb iets voor je: http://www.mongodb.org/

[...]

waarom zou dat hier precies helpen?

[TheNephilim]

@ cariolive23: Klopt, heb het nu gewijzigd en nu maakt ie wel gebruik van de index! Thnx

@ GlowMouse: utf-8 heb ik gekozen omdat dat wat netter stond ipv latin1_swedish_ci, maar heeft eigenlijk geen reden. Varchar is inderdaad te lang, die kunnen we pas inkorten als we zeker weten wat de maximale lengte is. Jah, die 2e kan inderdaad altijd nog opgesplitst worden, maar had eigenlijk gehoopt dat (bijv.) een index op de 3 kolommen zou werken.

@ Shroomy: Ik zal het eens bekijken, maar om nu een andere database te gaan gebruiken was niet de bedoeling eigenlijk.

[Voutloos]

Bernardo schreef op dinsdag 01 juni 2010 @ 12:33:
Jah, die 2e kan inderdaad altijd nog opgesplitst worden, maar had eigenlijk gehoopt dat (bijv.) een index op de 3 kolommen zou werken.

Hoe jij de werking van die index voor 3 kolommen voor je? Stel je voor dat je een kopietje van het telefoonboek zo zou moeten ordenen dat je snel de nummers kan vinden waar achternaam met N-Z begint of plaatsnaam met A-F of straatnaam met A-K, hoe zou je dat doen?

[cariolive23]

Voutloos schreef op dinsdag 01 juni 2010 @ 12:58:
[...]
Hoe jij de werking van die index voor 3 kolommen voor je? Stel je voor dat je een kopietje van het telefoonboek zo zou moeten ordenen dat je snel de nummers kan vinden waar achternaam met N-Z begint of plaatsnaam met A-F of straatnaam met A-K, hoe zou je dat doen?

Nu gebruik je een "of", met een "en" zou een index op 3 kolommen uitstekend kunnen werken. Het hangt er maar net vanaf, hoe ziet je data eruit en hoe zien je queries eruit. It all depends!

[Voutloos]

Uiteraard, maar de query uit startpost zou moeten filteren met of.

[TheNephilim]

Het probleem is inderdaad de OR, dat had ik nog niet zo bekeken. Zoals het er nu uitziet is 3 verschillende queries maken, met dus ook 3 verschillende indexes de enige manier om gebruik te maken van indexes.

Jammer, maar dan is het blijkbaar niet anders

[Voutloos]

Maar goed, als die query nu de meerderheid vd rows aanpast, kan het zo maar zijn dat de huidige vorm zonder index het snelst is.

Dus er komt ook een stukje meten=weten bij ipv blind een lijstje indexloze querirs afwerken.

[ Voor 26% gewijzigd door Voutloos op 01-06-2010 22:48 ]

[GlowMouse]

Voutloos schreef op dinsdag 01 juni 2010 @ 22:46:
Maar goed, als die query nu de meerderheid vd rows aanpast, kan het zo maar zijn dat de huidige vorm zonder index het snelst is.

Dus er komt ook een stukje meten=weten bij ipv blind een lijstje indexloze querirs afwerken.

Dat kan ik zo al voorspellen bij een query die maar eens in de 15 minuten draait en waar gebruikers niet op zitten te wachten. Maar zie ook mijn eerdere opmerking: meten is vrij onmogelijk.

[Kalentum]

Ben ik nu gek of...

Bernardo schreef op dinsdag 01 juni 2010 @ 11:09:

SQL:

UPDATE
  (`health` AS `h`
  INNER JOIN
    `stable` AS `s`
  USING
    (`id`))
SET
  `h`.`coat` = CASE WHEN (`h`.`coat` > 0) THEN `h`.`coat`-1 END,
  `h`.`hooves` = CASE WHEN (`h`.`hooves` > 0) THEN `h`.`hooves`-1 END,
  `h`.`manure` = CASE WHEN (`h`.`manure` < 100) THEN `h`.`manure`+1 END

Deze query heeft helemaal geen inperking. De database gaat dus altijd rij voor rij de boel doorploegen.

[Voutloos]

Zonder uit te sluiten dat je gek bent: Er is inderdaad uberhaupt geen restrictie of filter mogelijkheid.

[GlowMouse]

Voutloos schreef op donderdag 03 juni 2010 @ 20:57:
Zonder uit te sluiten dat je gek bent: Er is inderdaad uberhaupt geen restrictie of filter mogelijkheid.

Een WHERE zou mogelijk helpen via een index merge, maar drie losse queries zijn mogelijk sneller.

[_js_]

GlowMouse schreef op donderdag 03 juni 2010 @ 23:55:
[...]

Een WHERE zou mogelijk helpen via een index merge, maar drie losse queries zijn mogelijk sneller.

De originele query zet ook de waardes die niet voldoen aan de eisen op null, dus niet 3 maar 4 query's (wat mogelijk weer langzamer is dan die ene originele query.)

[TheNephilim]

De originele query heeft wel een inperking, deze heb ik eruit gehaald aangezien dit geen invloed heeft op het gebruik van indexes. De inperking houd in dat er een rij aanwezig moet zijn in de online tabel, hij werkt dus alleen de rijen af die horen bij mensen die online zijn op dat moment. Door middel van een INNER JOIN trouwens.

[cariolive23]

Bernardo schreef op vrijdag 04 juni 2010 @ 10:09:
... aangezien dit geen invloed heeft op het gebruik van indexes.

Dit kan een enorme invloed hebben, dankzij de inner join kan er een veel kleinere relevante dataset ontstaan. Zie EXPLAIN en ga stoeien met verschillende indexen, dan kun je dit zelf ook constateren.