Frage zur Abfrageperformance

[mdmc187]

Hallo zusammen,

ich habe eine Frage zu der Performance von MySQL bei einer Abfrage:

Angenommen ich habe eine MySQL Tabelle mit 3 Spalten (ID, Zahl1, Zahl2) und ca. 10 Millionen Einträgen
(alle paar Minuten werden bestimmte Zeilen gelöscht und neue hinzugefügt).

Wenn man jetzt eine Abfrage macht die wie folgt aussieht:

SELECT ID FROM Tabelle Where
(zahl 1 > 100 UND zahl 1 < 200)
UND
(zahl2 > 500 UND zahl2 < 1000)

(hier ginge natürlich auch "BETWEEN",)

Wird dann jede einzelne Zeile gegen die abfrage geprüft ? Also werden dann quasi mit jeder Abfrage (alle 30 Sekunden 1 Abfrage) 10 Millionen Zeilen geprüft um alle Zeilen mit dem passenden Ergebnis zu finden ?

Oder wird sowas irgendwie Performanter berechnet ?

Vielen Dank,
mdmc

 

[akretschmer]

MySQL kann je Abfrage und Tabelle nur einen Index nutzen. Mit PG z.B. könntest Du 2 Indexe anlegen und dann von Bitmap Index Scan profitieren.

 

[mdmc187]

Ich kann leider nur bei MySQL bleiben im Moment. Also werde ich mit langsamen Datenbankabfragen rechnen müssen ?

 

[akretschmer]

Möglicherweise. Hab mal auf meinem Reiselaptop (ungetunt) das mit deiner Tabelle und 10 Millionen Zeilen nachgebaut:

test=# create table foo(id int primary key, z1 int, z2 int);
CREATE TABLE
test=# insert into foo select s, random()*10000, random()*10000 from generate_series(1,10000000) s;
INSERT 0 10000000
test=# create index foo_a on foo(z1);
CREATE INDEX
test=# create index foo_b on foo(z2);
CREATE INDEX
test=# select * from foo order by id limit 5;
 id |  z1  |  z2  
----+------+------
  1 | 4349 |  738
  2 | 8085 | 1330
  3 | 8248 | 2381
  4 | 8069 | 6129
  5 | 6827 | 3216
(5 rows)

test=# explain analyse select * from foo where z1 between 100 and 200 and z2 between 500 and 1000;
  QUERY PLAN   
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12708.79..27886.80 rows=5092 width=12) (actual time=390.468..752.401 rows=4969 loops=1)
  Recheck Cond: ((z1 >= 100) AND (z1 <= 200) AND (z2 >= 500) AND (z2 <= 1000))
  Rows Removed by Index Recheck: 46920
  Heap Blocks: exact=24817
  ->  BitmapAnd  (cost=12708.79..12708.79 rows=5092 width=0) (actual time=379.087..379.087 rows=0 loops=1)
  ->  Bitmap Index Scan on foo_a  (cost=0.00..2117.05 rows=100861 width=0) (actual time=142.820..142.820 rows=100995 loops=1)
  Index Cond: ((z1 >= 100) AND (z1 <= 200))
  ->  Bitmap Index Scan on foo_b  (cost=0.00..10588.94 rows=504851 width=0) (actual time=218.688..218.688 rows=501649 loops=1)
  Index Cond: ((z2 >= 500) AND (z2 <= 1000))
 Planning time: 1.961 ms
 Execution time: 753.855 ms
(11 rows)

test=#

Out of the Box in 0.7 Sekunden. Mit größeren Shared Buffers ginge das noch schneller.

Ähm, noch schneller ist ein Index über beide Spalten:

test=# create index foo_c on foo(a,b);
ERROR:  column "a" does not exist
test=# create index foo_c on foo(z1,z2);
CREATE INDEX
test=# explain analyse select * from foo where z1 between 100 and 200 and z2 between 500 and 1000;
  QUERY PLAN   
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=2622.64..17800.66 rows=5092 width=12) (actual time=71.839..146.343 rows=4969 loops=1)
  Recheck Cond: ((z1 >= 100) AND (z1 <= 200) AND (z2 >= 500) AND (z2 <= 1000))
  Heap Blocks: exact=4762
  ->  Bitmap Index Scan on foo_c  (cost=0.00..2621.36 rows=5092 width=0) (actual time=67.149..67.149 rows=4969 loops=1)
  Index Cond: ((z1 >= 100) AND (z1 <= 200) AND (z2 >= 500) AND (z2 <= 1000))
 Planning time: 2.494 ms
 Execution time: 148.847 ms
(7 rows)

test=#

könnte vielleicht sogar in MySQL gehen.


Grüße aus London.

 

[ukulele]

Grundsätzlich sollte so ein Index über zahl1 erstmal schon Performance bringen, damit solltest du gezielt testen. Wenn es geht natürlich auch gerne ein Index auf zahl1,zahl2. In jedem Fall solltest du dich damit vertraut machen.

Außerdem läßt sich vielleicht am Konzept noch etwas verbessern so das nicht ständig in die DB geschrieben werden muss.