[MySQL] IN(col1, col2) + Offset, Limit optimieren

paddya · 20 Juli 2009

Guten Abend,

ich stehe grade vor dem Problem, eine Query nach folgendem Muster optimieren zu müssen:

PHP:

SELECT foo, bar, col1, col2 FROM table INNER JOIN bla ON table.foo = bla.foo WHERE 12 IN(col1, col2) ORDER BY bar DESC  LIMIT ($page-1)*25, 25

Ohne das blöde Offset würde ich die Abfrage einfach auf ein UNION ALL umschreiben...

Das Problem ist aber, zuverlässig immer die richtigen Datensätze zu liefern und beim Blättern nichts zu überspringen. Das Ganze soll natürlich so performant wie möglich sein...

Normalerweise würde ich das also so (ohne Offset) machen:

PHP:

(SELECT foo, bar, col1, col2 FROM table INNER JOIN bla ON table.foo = bla.foo WHERE col1 = 12 LIMIT 25) UNION ALL (SELECT foo, bar, col1, col2 FROM table INNER JOIN bla ON table.foo = bla.foo WHERE col2 = 12 LIMIT 25) ORDER BY bar DESC LIMIT 25

Sobald ich jedoch das Offset miteinbeziehen möchte, habe ich ein Problem bei der Vorselektion der Datensätze. Es kann dann unter Umständen so sein, dass auf Seite 1 12 Datensätze mit col1 = 12 und 13 Datensätze mit col2 = 12 angezeigt werden. Auf Seite zwei sollen die natürlich übersprungen werden. Mein Problem: Ich habe dann ein variables Offset für jede der beiden Möglichkeiten, das ich vor der Query bestimmen muss -> unperformant.

Zweite Möglichkeit: jedes Mal die letzte ID der vorherigen Seite in der URL mitschleppen und dann einfach nur ein einfaches LIMIT zu machen:

PHP:

(SELECT foo, bar, col1, col2 FROM table INNER JOIN bla ON table.foo = bla.foo WHERE col1 = 12 AND bar < MAX_ID LIMIT 25) UNION ALL (SELECT foo, bar, col1, col2 FROM table INNER JOIN bla ON table.foo = bla.foo WHERE col2 = 12 AND bar < MAX_ID LIMIT 25) ORDER BY bar DESC LIMIT 25

Oder seh ich die einfache Lösung gerade nur nicht? Die MAX_ID-Lösung würde ich gerne vermeiden, weil es dann mein System etwas inkosistent (die Seite ist untergliedert in verschiedene Auswertungen, wovon nur einige diese Optimierung bräuchten) machen würde...

Experten vor :ugly:

Greetz

paddya

transversalis · 20 Juli 2009

Vorsicht: Wenn in Spalte col1 und col2 jeweils 12 drin steht, dann liefern die erste und zweite Query unterschiedliche Ergebnisse.

ist der Inhalt der Spalte bar unique ?

sind bar und col1 bzw col2 in der gleichen tabelle oder in unterschiedlichen ?

paddya · 21 Juli 2009

transversalis schrieb:
Vorsicht: Wenn in Spalte col1 und col2 jeweils 12 drin steht, dann liefern die erste und zweite Query unterschiedliche Ergebnisse.

ist der Inhalt der Spalte bar unique ?

sind bar und col1 bzw col2 in der gleichen tabelle oder in unterschiedlichen ?

1. Ja, tun sie, weil die zweite Query (also das UNION ohne MAX-ID) das Offset nicht implementiert.

2. bar wäre in meinem Fall der Primärschlüssel, also UNIQUE

3. gleiche Tabelle (alles in der Tabelle table). Normalerweise würde ich noch Felder aus dem JOIN mitselektieren, allerdings wäre das hier dann zu lang geworden...

Ich glaube, ich muss wirklich meine zweite Variante mit der MAX-ID implementieren...

Danke schon mal,

paddya

theHacker · 21 Juli 2009

paddya schrieb:
Oder seh ich die einfache Lösung gerade nur nicht?

Weiß ich nicht, aber ich probiers mal einfach:

Code:

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] foo, bar, col1, col2
[B][COLOR=#9932cc]FROM[/COLOR][/B] table
[B][COLOR=#9932cc]INNER[/COLOR][/B] [B][COLOR=#9932cc]JOIN[/COLOR][/B] bla [B][COLOR=#9932cc]ON[/COLOR][/B] [COLOR=Black]table[/COLOR].foo = bla.foo
[B][COLOR=#9932cc]WHERE[/COLOR][/B] col1 = 12 [B][COLOR=#9932cc]OR[/COLOR][/B] col2 = 12
[B][COLOR=#9932cc]ORDER[/COLOR][/B] [B][COLOR=#9932cc]BY[/COLOR][/B] bar [B][COLOR=#9932cc]DESC[/COLOR][/B]
[B][COLOR=#9932cc]LIMIT[/COLOR][/B] ($page-1)*25, 25  [/FONT]

Wenn du dann Indizes auf col1 und col2 hast, müsste das doch schnell gehen :think:

Die Frage is, ob dein IN(...) nicht sowieso auf obiges abgebildet wird.

Kp, ob das jetzt geholfen hat :biggrin:

transversalis · 21 Juli 2009

paddya schrieb:
1. Ja, tun sie, weil die zweite Query (also das UNION ohne MAX-ID) das Offset nicht implementiert.

Nein, nicht deshalb.
Der Satz würde zweimal in der Ergebnismenge auftauchen.

Ich würde wohl auch die MAX-ID speichern, aber keinen UNION ALL verwenden. Die Query sähe dann so aus:

PHP:

SELECT foo, bar, col1, col2 
FROM table INNER JOIN bla 
ON table.foo = bla.foo 
WHERE ( col1 = 12 OR col2 = 12 )
AND bar < MAX_ID
ORDER BY bar DESC LIMIT 25

Das Problem bei Verwendung von MAX-ID ist allerdings, dass Du nicht mehr ohne weiteres zurückblättern kannst

ice-breaker · 21 Juli 2009

Für welchen Verwendungszweck ist das denn?
Denn Pagination auf Offset-Limit-Basis ist sowieso ineffizient, erst (als Beispiel) 1000 Ergebnisse verwerfen, um die nächsten 10 zu selecten.

Vllt kann man ja eine Lösung finden, die Pagination unterstützt, aber ohne Offset auskommt

paddya · 21 Juli 2009

theHacker schrieb:
Wenn du dann Indizes auf col1 und col2 hast, müsste das doch schnell gehen
Die Frage is, ob dein IN(...) nicht sowieso auf obiges abgebildet wird.

Das Problem ist, dass MySQL nen Fulltable-Scan hervorzaubert, weil es für index_col1 = const OR index_col2 = const keinen Index verwenden kann. Das ist das Problem bei der Sache...

transversalis schrieb:
Nein, nicht deshalb.
Der Satz würde zweimal in der Ergebnismenge auftauchen.

Nur, wenn col1 und col2 gleichzeitig 12 sind. Diesen Fall gibt es aber nicht

transversalis schrieb:
Das Problem bei Verwendung von MAX-ID ist allerdings, dass Du nicht mehr ohne weiteres zurückblättern kannst

Jep...

ice-breaker schrieb:
Für welchen Verwendungszweck ist das denn?

Bei einem Browsergame (scooter-game.de, wie so oft) sollen alle Rennen aufgelistet werden, an denen ein User X beteiligt war/ist.

RaceID|Herausforderer|Gegner

ice-breaker schrieb:
Denn Pagination auf Offset-Limit-Basis ist sowieso ineffizient, erst (als Beispiel) 1000 Ergebnisse verwerfen, um die nächsten 10 zu selecten.

Jep, je weiter man nach hinten blättert, desto ineffizienter wird die Query.

ice-breaker schrieb:
Vllt kann man ja eine Lösung finden, die Pagination unterstützt, aber ohne Offset auskommt

Ja, da muss ich mich mal drüber nachdenken und etwas googeln.

Die beste Variante wäre natürlich, wenn jemand MySQL beibringt, für solche "col1 = const OR col2 = const"-Statements einen Index zu verwenden.

Danke schon mal für euren Input, werde weiter drüber grübeln.

Greetz

paddya

ice-breaker · 21 Juli 2009

paddya schrieb:
Das Problem ist, dass MySQL nen Fulltable-Scan hervorzaubert, weil es für index_col1 = const OR index_col2 = const keinen Index verwenden kann. Das ist das Problem bei der Sache...

in MySQL5 wird aber meine ich daraus ein Union gebaut, von daher sollte der Index genutzt werden.

paddya schrieb:
Bei einem Browsergame (scooter-game.de, wie so oft) sollen alle Rennen aufgelistet werden, an denen ein User X beteiligt war/ist.

RaceID|Herausforderer|Gegner

wie wäre es mit sowas:

Code:

CREATE TABLE IF NOT EXISTS `scooter_races` (
  `driver_id` int(10) unsigned NOT NULL,
  `race_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`driver_id`,`race_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT x, y
FROM scooter_races
JOIN scooter_raceresult USING(race_id)
WHERE driver_id = 5 AND race_id <= 5000
LIMIT 30

wenn du dann eine Seite weiter springen willst, ist die race_id < dem letzten result auf der momentanen Seite.
Man müsste ma testen wie das mit dem Index auf race_id klappt, eventuell muss man es als MAX_INT - race_id speichern um die Daten auch in aufsteigender Reihenfolge zu haben.

paddya · 21 Juli 2009

ice-breaker schrieb:
in MySQL5 wird aber meine ich daraus ein Union gebaut, von daher sollte der Index genutzt werden.

Schön wäre es, offensichtlich aber nicht.

PHP:

SELECT *
FROM races
WHERE x = challenger
OR x = opponent

Gibt es eine Quelle dafür, dass MySQL das machen soll?

ice-breaker schrieb:

wie wäre es mit sowas:

Code:

CREATE TABLE IF NOT EXISTS `scooter_races` (
  `driver_id` int(10) unsigned NOT NULL,
  `race_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`driver_id`,`race_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[/QUOTE]

Die Idee hatte ich auch schon und das werde ich mit dem nächsten größeren Update wohl auch anstreben.

[QUOTE="ice-breaker, post: 4740625, member: 1955"]
wenn du dann eine Seite weiter springen willst, ist die race_id < dem letzten result auf der momentanen Seite.
Man müsste ma testen wie das mit dem Index auf race_id klappt, eventuell muss man es als MAX_INT - race_id speichern um die Daten auch in aufsteigender Reihenfolge zu haben.[/QUOTE]

Problem ist nach wie vor das Zurückblättern... Aber das Problem erledigt sich ja eh, weil ich dann nur noch ein normales [FONT="Courier New"]col = const[/FONT] habe und MySQL nen Index nutzen kann.

Danke :)

paddya

ice-breaker · 21 Juli 2009

paddya schrieb:
Schön wäre es, offensichtlich aber nicht.

[...]

Gibt es eine Quelle dafür, dass MySQL das machen soll?

Das Verfahren hat sich mitlerweile wieder geändert, wird zu einem Range-Scan, also auch mit einem Index nutzbar.
Quellen habe ich gerade keine parat, ich denke aber mal der Query-Optimizer hat sich bei dir entschieden, dass ein Fulltable-Scan schneller ist

Ein FORCE INDEX sollte Klarheit schaffen.

paddya · 21 Juli 2009

ice-breaker schrieb:
Das Verfahren hat sich mitlerweile wieder geändert, wird zu einem Range-Scan, also auch mit einem Index nutzbar.
Quellen habe ich gerade keine parat, ich denke aber mal der Query-Optimizer hat sich bei dir entschieden, dass ein Fulltable-Scan schneller ist
Ein FORCE INDEX sollte Klarheit schaffen.

Unter MySQL 5.0.45 bringt auch ein FORCE INDEX nichts. Der Index wird zwar als "possible key" eingestuft, verwendet werden kann er aber nicht.

MySQL-Doku (5.1 und 5.0 sind da identisch) sagt auch:

PHP:

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

Greetz

paddya

ice-breaker · 21 Juli 2009

kann ich definitiv nicht bestätigen:

mysql> SELECT VERSION()\G
*************************** 1. row ***************************
VERSION(): 5.0.51a
1 row in set (0.00 sec)

mysql> DESCRIBE int_big\G
*************************** 1. row ***************************
Field: id
Type: int(11) unsigned
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 2. row ***************************
Field: value
Type: int(11) unsigned
Null: NO
Key: MUL
Default: NULL
Extra:
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE int_big\G
*************************** 1. row ***************************
Table: int_big
Create Table: CREATE TABLE `int_big` (
`id` int(11) unsigned NOT NULL,
`value` int(11) unsigned NOT NULL,
KEY `value` (`value`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM int_big\G
*************************** 1. row ***************************
COUNT(*): 2000000
1 row in set (15.48 sec)

mysql> EXPLAIN SELECT * FROM int_big WHERE id = 574405 OR value = 928999\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: int_big
type: index_merge
possible_keys: value,id
key: id,value
key_len: 4,4
ref: NULL
rows: 6
Extra: Using union(id,value); Using where
1 row in set (0.06 sec)

paddya · 21 Juli 2009

ice-breaker schrieb:
kann ich definitiv nicht bestätigen[...]

Okay, dann scheint das irgendwo zwischen 5.0.45 und 5.0.51a geändert worden zu sein.

Mal ne ganz andere Frage: lohnt sich performance- und featuretechnisch ein Upgrade auf 5.1.x?

Greetz

paddya

ice-breaker · 21 Juli 2009

wenn du kein InnoDB(InnoDB Plugin), keine Replikation oder Partitionierung nutzt und das Slow Querylog nicht millisekundengenau sein muss, gibt es keine allzugroßen Änderungen.
Verbesserungen sind natürlich immer in den Algorithmen und so drinne, zu 5.1 aber nix weltbewegendes.

[MySQL] IN(col1, col2) + Offset, Limit optimieren

paddya

Well-known member

transversalis

Well-known member

paddya

Well-known member

theHacker

sieht vor lauter Ads den Content nicht mehr

transversalis

Well-known member

ice-breaker

return void

paddya

Well-known member

ice-breaker

return void

paddya

Well-known member

ice-breaker

return void

paddya

Well-known member

ice-breaker

return void

paddya

Well-known member

ice-breaker

return void