|
26.02.2017, 19:25
#39410442
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Здравствуйте, столкнулся с такой проблемой
Есть таблицы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
--
-- Структура таблицы `cars`
--
CREATE TABLE IF NOT EXISTS `cars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`body_id` tinyint(3) unsigned NOT NULL,
`first_image_id` int(10) unsigned NOT NULL,
`car_classes_id` tinyint(3) unsigned NOT NULL,
`fuel_id` tinyint(3) unsigned NOT NULL,
`trns_id` tinyint(3) unsigned NOT NULL,
`drive_id` tinyint(3) unsigned NOT NULL,
`year_car` smallint(5) unsigned NOT NULL,
`price` decimal(10,2) unsigned NOT NULL,
`text` text NOT NULL,
`status` enum('0','1') NOT NULL DEFAULT '0',
`premium` enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `first_image_id` (`first_image_id`),
KEY `status` (`status`),
KEY `premium` (`premium`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
--
-- Структура таблицы `lang_car_body` (кузов)
--
CREATE TABLE IF NOT EXISTS `lang_car_body` (
`id_car_body` tinyint(3) unsigned NOT NULL,
`id_lang` tinyint(3) unsigned NOT NULL,
`title` varchar(50) NOT NULL,
PRIMARY KEY (`id_car_body`,`id_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
--
-- Структура таблицы `lang_drv` (привод)
--
CREATE TABLE IF NOT EXISTS `lang_drv` (
`id_drv` tinyint(3) unsigned NOT NULL,
`id_lang` tinyint(3) unsigned NOT NULL,
`title` varchar(50) NOT NULL,
PRIMARY KEY (`id_drv`,`id_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
--
-- Структура таблицы `lang_trns` (Коробка передач)
--
CREATE TABLE IF NOT EXISTS `lang_trns` (
`id_trns` tinyint(3) unsigned NOT NULL,
`id_lang` tinyint(3) unsigned NOT NULL,
`title` varchar(50) NOT NULL,
PRIMARY KEY (`id_trns`,`id_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
--
-- Структура таблицы `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`sorname` varchar(100) NOT NULL,
`logo` varchar(255) NOT NULL,
`email` varchar(150) NOT NULL,
`phone` varchar(20) NOT NULL,
`pass` varchar(255) NOT NULL,
`salt` varchar(255) NOT NULL,
`vert` enum('0','1') NOT NULL DEFAULT '0',
`vert_code` varchar(255) NOT NULL,
`vert_pass_code` varchar(255) NOT NULL,
`secret_key` varchar(150) NOT NULL,
`ban` enum('0','1') NOT NULL DEFAULT '0',
`role` enum('1','2','3') NOT NULL,
`subs` enum('0','1') NOT NULL DEFAULT '0',
`csrf_token_key` varchar(255) NOT NULL,
`admission` enum('0','1') NOT NULL DEFAULT '0',
`ip_adr` varchar(30) NOT NULL,
`reg_date` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`,`phone`),
KEY `vert` (`vert`),
KEY `ban` (`ban`),
KEY `role` (`role`),
KEY `admission` (`admission`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
--
-- Структура таблицы `brands`
--
CREATE TABLE IF NOT EXISTS `brands` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`level` tinyint(3) unsigned NOT NULL DEFAULT '1',
`left_key` smallint(5) unsigned NOT NULL,
`right_key` smallint(5) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `left_key` (`left_key`),
UNIQUE KEY `right_key` (`right_key`),
KEY `key_list` (`left_key`,`right_key`,`level`),
KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=126 ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
--
-- Структура таблицы `car_images`
--
CREATE TABLE IF NOT EXISTS `car_images` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`car_id` int(10) unsigned NOT NULL,
`img` varchar(255) NOT NULL,
`thumb_img` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `car_id` (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;
Есть страница поиска с фильтрацией по разным характеристикам
Запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
EXPLAIN EXTENDED SELECT c.id, b1.title modeltitle, b2.title brandtitle, car_images.thumb_img, c.price, lang_car_body.title bodytitle,
lang_trns.title trnstitle, lang_drv.title drvtitle, c.price_week, c.price_month, c.year_car, c.casco FROM cars c
INNER JOIN brands b1 ON c.parent_id = b1.id
INNER JOIN brands b2 ON b2.left_key < b1.left_key AND b2.right_key > b1.right_key AND b2.level = 1
INNER JOIN car_images ON car_images.id = c.first_image_id
INNER JOIN lang_trns ON lang_trns.id_trns = c.trns_id
INNER JOIN lang_car_body ON lang_car_body.id_car_body = c.body_id
INNER JOIN lang_drv ON lang_drv.id_drv = c.drive_id
INNER JOIN users ON c.user_id = users.id
WHERE lang_trns.id_lang = "1" AND lang_car_body.id_lang = "1" AND lang_drv.id_lang = "1" AND c.status = "1"
ORDER BY c.id DESC LIMIT 0, 15
Выдает что идет сканирование всей таблицы cars (10150 записей). Просьба помочь, как правильно расставить индексы. Спасибо |
|
|
