MYSQL: SELECT * FROM WHERE benutzt keine Indizes

[aymanghost]

Hallo kan mir jemand sagen warum bei einer SELECT Anweisung in dieser Tabelle der Index time nicht verwendet wird.

Hier die Anweisung:

SELECT sid FROM sessions24 WHERE time > 1228342659  AND username <> '' ORDER BY class DESC, username ASC;

Hier die Tabelle:

+-------------+---------------------+------+-----+-------------------------------------------------+-------+
| Field       | Type                | Null | Key | Default                                         | Extra |
+-------------+---------------------+------+-----+-------------------------------------------------+-------+
| sid         | varchar(32)         | NO   | PRI |                                                 |       |
| sesskey     | tinyint(32)         | NO   | PRI |                                                 |       |
| expiry      | int(11) unsigned    | NO   |     | 0                                               |       |
| expireref   | varchar(64)         | NO   |     | 0                                               |       |
| data        | varchar(120)        | NO   |     | -                                               |       |
| uid         | int(10)             | NO   | MUL |                                                 |       |
| username    | varchar(40)         | NO   | MUL |                                                 |       |
| class       | tinyint(4)          | NO   | MUL |                                                 |       |
| ip          | varchar(40)         | NO   | MUL |                                                 |       |
| time        | int(11)             | YES  | MUL | NULL                                            |       |
| url         | varchar(150)        | NO   |     | /index.php                                      |       |
| useragent   | varchar(120)        | NO   |     | MS                                              |       |
| parked      | enum('yes','no')    | NO   |     | no                                              |       |
| warned      | enum('yes','no')    | NO   |     | no                                              |       |
| enabled     | enum('yes','no')    | NO   |     | no                                              |       |
| avatar      | varchar(100)        | NO   |     | default_avatar.gif |       |
| added       | datetime            | NO   |     | 0000-00-00 00:00:00                             |       |
| gender      | enum('1','2','3')   | NO   |     | 1                                               |       |
| last_access | datetime            | NO   |     | 0000-00-00 00:00:00                             |       |
+-------------+---------------------+------+-----+-------------------------------------------------+-------+

Und hier EXPLAIN:

+----+-------------+------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | sessions24 | ALL  | time,username | NULL | NULL    | NULL | 10148 | Using where; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

 

[Huschi]

kan mir jemand sagen warum bei einer SELECT Anweisung in dieser Tabelle der Index time nicht verwendet wird.

Gegenfrage:
Was läßt Dich vermuten, daß der Index nicht genutzt wird?

| sid         | varchar(32)         | NO   | PRI |                                                 |       |
| sesskey     | tinyint(32)         | NO   | PRI |                                                 |       |

Hier ist definitiv ein Fehler:
Du darfst nicht (auch wenn es hier aus irgendeinem Grund ging) zwei Felder als Primary Key nutzten. Es sollte nur einer ein PK sein und der andere UNIQUE.

| time        | int(11)             | YES  | MUL | NULL                                            |       |

Zweiter Fehler:
Zwar wird ein KEY auf "time" gesetzt, aber gleichzeitig erlaubt, daß der Wert NULL sein darf. Damit wird die Effizienz verringert und die Logik zerstört.

PS: Ich weiß ja nicht, wie es Anderen geht, aber ich kann ein CREATE-Statement leichter lesen.

huschi.

 

[aymanghost]

Danke für die Antwort Huschi,

hab mal NULL entfernt und PRIMARY KEY nur auf SID gesetzt.

Wenn ich jetzt ein SELCT ausführe in dem time (grösser) $vergangen ist verändert sich nichts.

Das kommt bei raus.

EXPLAIN SELECT COUNT(*) FROM sessions24 WHERE time >= 1228387244 ORDER BY class DESC, username ASC;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sessions24 | ALL  | time          | NULL | NULL    | NULL |   95 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

type = ALL
key = NULL

Wenn ich die Abfrage ändere in time (kleiner) $vergangen

dann kommt das raus:

EXPLAIN SELECT sid FROM sessions24 WHERE time < 1228387244 ORDER BY class DESC, username ASC;
+----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | sessions24 | range | time          | time | 4       | NULL |    1 | Using where; Using filesort |
+----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

type = range
Key = time

Aber in diese Richtung will ich ja nicht suchen, sondern

time > (time()-1200)

Die Tabellenstruktur sieht übrigens so aus:

CREATE TABLE `sessions24` (
  `sid` varchar(32) NOT NULL default '',
  `sesskey` tinyint(32) NOT NULL,
  `expiry` int(11) unsigned NOT NULL default '0',
  `expireref` varchar(64) NOT NULL default '0',
  `data` varchar(120) NOT NULL default '-',
  `uid` int(10) NOT NULL,
  `username` varchar(40) NOT NULL,
  `class` tinyint(4) NOT NULL,
  `ip` varchar(40) NOT NULL,
  `time` int(11) NOT NULL,
  `url` varchar(150) NOT NULL default '/index.php',
  `useragent` varchar(120) NOT NULL default 'MS',
  `warned` enum('yes','no') NOT NULL default 'no',
  `enabled` enum('yes','no') NOT NULL default 'no',
  `avatar` varchar(100) NOT NULL default 'default_avatar.gif',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `gender` enum('1','2','3') NOT NULL default '1',
  `last_access` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`sid`),
  KEY `uid` (`uid`),
  KEY `time` (`time`),
  KEY `ip` (`ip`),
  KEY `username` (`username`),
  KEY `class` (`class`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=COMPRESSED;

 

[elias5000]

Ich bin jetzt nicht so der Spezialist für MySQL, aber bei Datenbanken ist es nicht unbedingt notwendig, einen Index zu benutzen, um schnell zu sein.
Der Query-Planner entscheidet selbst, wann es sinnvoll ist und wann nicht.

Nicht sinnvoll ist ein Query z.B. dann, wenn eine Tabelle nur eine Page besitzt. Da die Engine sowieso diese Page durchwandern muss, wäre ein indexed Search nur zusätzlicher Aufwand, da die Anzahl der zu bearbeitenden Tablepages durch einen solchen nicht weiter minimiert werden kann.

Deshalb ist es wichtig, Query-Optimierung auf echten Daten(-mengen) durchzuführen.

 

[DeepThought]

Zunächst ist es natürlich Quatch, dass in einer relationalen Datenbank nur eine Spalte das primary Attribut haben darf. Ferner darf selbstverständlich ein Index auf eine nullable Spalte gesetzt werden.

Ich weiß nicht wie dieses Halbwissen in die Welt kommt, aber es hält sich hartnäckig.

Dein Problem liegt schlicht daran, dass der Where-Clause in dem Select über die Spalten time UND user geht. Wenn Du die MySQL Dokumentation zu Index-Usage und Selects sorgfältig liest, wirst Du sehen, dass Du dann einen zusammengesetzten Index brauchst UND die Reihenfolge der beiden Spalten im Index und Select einhalten musst.
In deinem Fall wäre es natürlich besser einen Subselect zu verwenden, dann brauchst Du nicht so einen komischen Index zu definieren, der das System ja auch etwas langsamer macht.

------
42