[MySQL] Datetime veld in Innodb gebruikt index niet

[urk_forever]

Hallo allemaal,

Wij hebben een applicatie die zijn data in MySQL opslaat. Nu zijn dit op dit moment MyISAM tabellen, maar door wat problemen met concurrent INSERT's en SELECT's ben ik aan het testen met InnoDB en nu loop ik tegen wat raars aan. Het gaat om de volgende query:

SQL:

SELECT * FROM tabel WHERE DATE(datumtijdveld) BETWEEN 2008-06-17 AND 2008-06-18

Met de MyISAM tabel gaat dit snel en gebruikt hij de Index die op het datumtijdveld is gedefinieerd. Vervolgens heb ik de tabel omgezet naar InnoDB en run ik dezelfde query, maar nu gebruikt hij de index niet.
Hij gebruikt de index pas als ik de query zo geef:

SQL:

SELECT * FROM tabel WHERE datumtijdveld BETWEEN '2008-06-17 00:00:00' AND '2008-06-18 23:59:59'

Ik heb al gezocht op internet en de mysql site maar kan niet vinden waarom dat is? Heeft iemand dit misschien eerder mee gemaakt?

[Alain]

Volgens mij heeft het een diepere achtergrond waarom de optimizer wel of niet voor de index kiest. Jouw testcase gebruikt bij mij gewoon beide keren de index:

code:

mysql> CREATE TABLE tabelMyIsam (datumtijdveld DATETIME);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE tabelinnoDB (datumtijdveld DATETIME) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tabelinnoDB VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelinnoDB VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelinnoDB VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelinnoDB VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelMyIsam VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelMyIsam VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelMyIsam VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tabelMyIsam VALUES ('2008-06-20');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE INDEX I_datumtijdveld1 ON tabelMyIsam (datumtijdveld);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX I_datumtijdveld2 ON tabelinnoDB (datumtijdveld);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM tabelMyIsam WHERE DATE(datumtijdveld) BETWEEN '2008-06-17' AND '2008-06-18';
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tabelMyIsam | index | NULL          | I_datumtijdveld1 | 9       | NULL |    4 | Using where; Using index |
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tabelinnoDB WHERE DATE(datumtijdveld) BETWEEN '2008-06-17' AND '2008-06-18';
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tabelinnoDB | index | NULL          | I_datumtijdveld2 | 9       | NULL |    4 | Using where; Using index |
+----+-------------+-------------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

[Voutloos]

Geef aub de output van explain.

Overigens is het wel zo handig om in de 1e query wel quotes om die datums te zetten, anders zou je nog kunnen denken dat het een sommetje is.

[urk_forever]

Nou, ik heb even een testje gemaakt als volgt:

SQL:

DROP TABLE IF EXISTS `mytest`.`opdrachten`;
CREATE TABLE  `mytest`.`opdrachten` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `geplandestartdatum` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_geplandestartdatum` (`geplandestartdatum`)
) ENGINE=MyISAM;

Hierin de volgende records toegevoegd:

SQL:

INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-01');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-02');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-03');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-04');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-05');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-06');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-07');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-08');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-09');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-10');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-11');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-12');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-13');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-14');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-15');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-16');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-17');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-18');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-19');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-20');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-21');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-22');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-23');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-24');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-25');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-26');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-27');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-28');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-29');
INSERT INTO opdrachten (geplandestartdatum) VALUES ('2008-06-30');

En dan de volgende queries:

SQL:

EXPLAIN SELECT * FROM opdrachten o where geplandestartdatum BETWEEN '2008-06-05 00:00:00' AND '2008-06-15 23:59:59';

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1, 'SIMPLE', 'o', 'ALL', 'idx_geplandestartdatum', '', '', '', 990, 'Using where'

En

SQL:

EXPLAIN SELECT * FROM opdrachten o where DATE(geplandestartdatum) BETWEEN '2008-06-05' AND '2008-06-15';

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1, 'SIMPLE', 'o', 'ALL', '', '', '', '', 990, 'Using where'

Zoals je ziet wordt in de 2de query de index niet eens overwogen En dit is op MyISAM nog niet eens op InnoDB.

[DizzyWeb]

Ik zie toch wel een duidelijk verschil tussen AlainS' voorbeeld en jouw voorbeeld hier. Jij zet een key, AlainS zet daadwerkelijk een index...

[urk_forever]

Nou, dat ligt dan aan de manier waarop de Query Browser het create statement maakt. Ik heb zojuist de index verwijderd en weer opnieuw aangemaakt, dan gebruikt hij dit commando :

SQL:

ALTER TABLE `opdrachten` ADD INDEX idx_datum(geplandestartdatum)

En dan krijg ik hetzelfde resultaat. Dus dat is het probleem niet lijkt mij.

[ Voor 5% gewijzigd door urk_forever op 19-06-2008 10:32 ]

[Alain]

Ik denk niet dat het een 'probleem' is, maar gewoon een keuze van de optimizer die wat ongelukkig uitvalt. De keuze is afhankelijk van de databasestructuur, populatie en versie van de rdms. Mocht je echt problemen krijgen kun je in het uiterste geval een index forceren, al kan dit natuurlijk nog ongelukkiger uitpakken omdat je twee verschillende datatypes wilt vergelijken en er dus sowieso een conversie gedaan moet worden.