FB3
Новичок
Почему MySQL не использует индекс при JOIN?
Имеются два таблицы:
[sql]CREATE TABLE `stat_installations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`player_id` int(10) NOT NULL,
`source` int(10) NOT NULL DEFAULT '0',
`referrer` int(10) unsigned NOT NULL DEFAULT '0',
`date` datetime NOT NULL,
`date_index` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `date_index` (`date_index`),
KEY `player_id` (`player_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `stat_preloader_visits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`player_id` int(10) unsigned NOT NULL,
`owner_id` int(10) unsigned NOT NULL,
`installed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`from_wall` tinyint(3) unsigned NOT NULL DEFAULT '0',
`referrer` int(10) unsigned NOT NULL DEFAULT '0',
`date` datetime NOT NULL,
`date_index` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `date_index` (`date_index`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;[/sql]
Запрос без JOIN использует индекс:
[sql]EXPLAIN
SELECT
COUNT(*) AS 'cnt',
spv.`date_index` 'date'
FROM `stat_preloader_visits` spv
WHERE spv.`date_index` BETWEEN '2010-07-12' AND '2010-08-12'
GROUP BY spv.`date_index`;[/sql]
Запрос с присоединением другой таблицы не использует индекс в начальной таблице:
[sql]EXPLAIN
SELECT
COUNT(*) AS 'cnt',
spv.`date_index` 'date'
FROM `stat_preloader_visits` spv,
`stat_installations` si
WHERE spv.`date_index` BETWEEN '2010-07-12'
AND '2010-08-12'
AND spv.`player_id` = si.`player_id`
GROUP BY spv.`date_index`;[/sql]
Собственно, не могу понять почему...
Имеются два таблицы:
[sql]CREATE TABLE `stat_installations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`player_id` int(10) NOT NULL,
`source` int(10) NOT NULL DEFAULT '0',
`referrer` int(10) unsigned NOT NULL DEFAULT '0',
`date` datetime NOT NULL,
`date_index` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `date_index` (`date_index`),
KEY `player_id` (`player_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `stat_preloader_visits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`player_id` int(10) unsigned NOT NULL,
`owner_id` int(10) unsigned NOT NULL,
`installed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`from_wall` tinyint(3) unsigned NOT NULL DEFAULT '0',
`referrer` int(10) unsigned NOT NULL DEFAULT '0',
`date` datetime NOT NULL,
`date_index` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `date_index` (`date_index`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;[/sql]
Запрос без JOIN использует индекс:
[sql]EXPLAIN
SELECT
COUNT(*) AS 'cnt',
spv.`date_index` 'date'
FROM `stat_preloader_visits` spv
WHERE spv.`date_index` BETWEEN '2010-07-12' AND '2010-08-12'
GROUP BY spv.`date_index`;[/sql]
Код:
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | spv | index | date_index | date_index | 4 | NULL | 4844330 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+[sql]EXPLAIN
SELECT
COUNT(*) AS 'cnt',
spv.`date_index` 'date'
FROM `stat_preloader_visits` spv,
`stat_installations` si
WHERE spv.`date_index` BETWEEN '2010-07-12'
AND '2010-08-12'
AND spv.`player_id` = si.`player_id`
GROUP BY spv.`date_index`;[/sql]
Код:
+----+-------------+-------+------+---------------+-----------+---------+------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+------------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | spv | ALL | date_index | NULL | NULL | NULL | 4844330 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | si | ref | player_id | player_id | 4 | club_stat_test.spv.player_id | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+-----------+---------+------------------------------+---------+----------------------------------------------+
Я думаю, что оно помрет и совсем перестанет работать через месяц-другой 