MySQL index für Spalten mit numerischen macht die Abfrage langsamer

Habe ich ein Optimierungs problem mit einem ziemlich großen Tabelle (~1,7 M Zeilen).

Gibt es zwei Spalten, bei der Auswahl von Zeilen, nennen wir Sie colA und colB. Sie sind beide vom Typ 'double' (5 Dezimalstellen) und reichen von:

colA: -90 ~ 90
colB: -180 ~ 180

Ohne einen index, jede Abfrage der form:

SELECT * FROM table where colA BETWEEEN a and b AND colB BETWEEN c and d

dauert ungefähr die gleiche Zeit zu laufen (~ 1 Sekunde), unabhängig von der Reichweite von (a,b) und (c,d) (als MySQL hat zu prüfen, jede Zeile).

Wenn ich einen index hinzuzufügen, colA und colB, passieren zwei Dinge: Abfragen, in denen der (a,b) & (c,d) - Reihe sind klein, zum Beispiel:

SELECT * FROM table where colA BETWEEEN -4 and 4 AND colB BETWEEN 3 and 7

laufen sehr schnell (~ 1/10 Sekunde). Jedoch ist die Ausführungszeit erhöht sich mit dem Bereich zwischen den abgefragten Werte. Zum Beispiel:

 SELECT * FROM table where colA BETWEEEN -80 and 80 AND colB BETWEEN -150 and 150

dauert etwa eine minute, um Sie auszuführen.

Ich weiß, wie B-Bäume, Werke für Streicher, aber ich bin nicht sicher, dass der Mechanismus, wenn die Daten numerisch und die Abfrage erfolgt mit einer Palette.

Wenn jemand könnte vorschlagen, wie optimieren diese Abfrage wäre ich dankbar. Ein Gedanke ist zum verwenden des index für kleine Bereiche und teilen MySQL, es nicht zu verwenden für die größeren, aber ich konnte nicht finden, dass ein Befehl, der dies ermöglicht.

Dank

EDIT: das erklärt

Es ist etwas, was ich blöderweise vergessen habe zu erwähnen. Die Ergebnisse sind sortiert nach rand() - ich bin mir bewusst, wie ineffizient das ist, aber ich konnte sehen keinen anderen Weg, um eine begrenzte Anzahl von Zeilen aus der Tabelle nach dem Zufallsprinzip.

Hinzufügen von rand() hat keinen Einfluss auf die Ausführung der Zeit, wenn kein index vorhanden ist, aber drastisch und erhöht die Zeit, die genommen, wenn es ist.

EDIT2: dies ist die Verwendung von zusammengesetzten Indizes.

KLEINE AUSWAHL:

"explain select * from table where-colA zwischen 35 und 38 und colB zwischen -10 und 5 ORDER BY RAND() LIMIT 20"

9783 Zeilen

NO INDEX (schnell)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

MIT INDEX (sehr schnell)

+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | table | range | test          | test | 18      | NULL | 136222 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

GROßE AUSWAHL:

"explain select * from table where-colA zwischen -80 und 80 und colB zwischen -150 und 150 ORDER BY RAND() LIMIT 20;"

1631862 Zeilen

NO INDEX (schnell)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

MIT INDEX (sehr langsame: > 60 Sekunden)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | test          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

EDIT3:

Zusammenfassen:(alle Abfragen beschränkt auf Rückkehr 20 Zeilen)

große Auswahl mit rand() mit index: 45 Sekunden

große Auswahl, ohne rand(), mit index: 0.003 Sekunden

große Auswahl mit rand, kein index: 1 Sekunde

große Reichweite ohne rand, kein index: 0.003 Sekunden

Die Anomalie ist: "der große range mit rand() mit index, 45 Sekunden".