Доброго времени суток!
Решил через EXPLAIN посмотреть запрос и удивился, когда в графе keys увидел NULL. На данный момент используется MariaDB (5.5.5-10.0.12-MariaDB-1~wheezy-log). Если выполнить запрос на MySQL, то индексы в графе keys присутствуют.
Даже если в запросе указать USE INDEX(индекс), все-равно в графе keys у MariaDB стоит NULL.
MariaDB не использует данные индексы или просто возвращает неверную информацию?
EXPLAIN SELECT SQL_NO_CACHE *
FROM fd_awards_ceremonies cer
LEFT JOIN fd_awards_nominees n ON(cer.ceremony_id = n.ceremony_id)
LEFT JOIN fd_awards_nominations nom ON(nom.nomination_id = n.nomination_id AND nom.nomination_general = 1)
LEFT JOIN fd_content c ON(n.content_id = c.content_id)
WHERE cer.award_id = 1
Результат:
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------------------------------------------+
| 1 | SIMPLE | cer | ALL | award_id | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | n | ALL | ceremony_id | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | nom | eq_ref | PRIMARY | PRIMARY | 4 | db.n.nominee_id | 1 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 3 | db.n.content_id | 1 | Using where |
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------------------------------------------+
4 rows in set (0.00 sec)
Вот структура таблиц fd_awards_ceremonies и fd_awards_nominees, относительно которых у меня вопрос.
CREATE TABLE `fd_awards_ceremonies` (
`ceremony_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`award_id` mediumint(8) unsigned NOT NULL,
`ceremony_year` year(4) DEFAULT NULL,
`ceremony_country` smallint(5) unsigned NOT NULL,
`ceremony_location` varchar(255) NOT NULL,
`ceremony_hosts` varchar(255) DEFAULT NULL,
`ceremony_hosts_text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ceremony_id`),
UNIQUE KEY `award_id` (`award_id`,`ceremony_year`),
KEY `award_country` (`ceremony_country`),
KEY `ceremony_id` (`ceremony_id`),
CONSTRAINT `fd_awards_ceremonies_ibfk_2` FOREIGN KEY (`ceremony_country`) REFERENCES `fd_countries` (`country_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fd_awards_ceremonies_ibfk_1` FOREIGN KEY (`award_id`) REFERENCES `fd_awards` (`award_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `fd_awards_nominees` (
`nominee_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nomination_id` int(10) unsigned NOT NULL,
`content_id` mediumint(8) unsigned NOT NULL,
`person_id` mediumint(8) unsigned NOT NULL,
`award_id` mediumint(8) unsigned NOT NULL,
`ceremony_id` mediumint(8) unsigned NOT NULL,
`award_status` tinyint(3) unsigned NOT NULL,
`person_text` varchar(255) DEFAULT NULL,
`content_text` varchar(255) DEFAULT NULL,
`song_text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`nominee_id`),
KEY `award_id` (`award_id`),
KEY `ceremony_id` (`ceremony_id`) USING BTREE,
KEY `nomination_id` (`nomination_id`),
CONSTRAINT `fd_awards_nominees_ibfk_3` FOREIGN KEY (`nomination_id`) REFERENCES `fd_awards_nominations` (`nomination_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fd_awards_nominees_ibfk_1` FOREIGN KEY (`award_id`) REFERENCES `fd_awards` (`award_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fd_awards_nominees_ibfk_2` FOREIGN KEY (`ceremony_id`) REFERENCES `fd_awards_ceremonies` (`ceremony_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Спасибо
UPD: упустил связующий стобец, обновил запрос и структуру таблиц. К сожалению, вопрос актуален.
Средний
