Как оптимизировать медленный запрос со многими объединениями

Моя ситуация:

запрос ищет около 90 000 автомобилей.
запрос занимает много времени каждый раз
У меня уже есть индексы по всем полям, в которые вы подключались.

Как его оптимизировать?

Вот запрос:

SELECT vehicles.make_id,
       vehicles.fuel_id,
       vehicles.body_id,
       vehicles.transmission_id,
       vehicles.colour_id,
       vehicles.mileage,
       vehicles.vehicle_year,
       vehicles.engine_size,
       vehicles.trade_or_private,
       vehicles.doors,
       vehicles.model_id,
       Round(3959 * Acos(Cos(Radians(51.465436)) *
                         Cos(Radians(vehicles.gps_lat)) *
                                           Cos(
                                           Radians(vehicles.gps_lon) - Radians(
                                           -0.296482)) +
                               Sin(
                                      Radians(51.465436)) * Sin(
                               Radians(vehicles.gps_lat)))) AS distance
FROM   vehicles
       INNER JOIN vehicles_makes
         ON vehicles.make_id = vehicles_makes.id
       LEFT JOIN vehicles_models
         ON vehicles.model_id = vehicles_models.id
       LEFT JOIN vehicles_fuel
         ON vehicles.fuel_id = vehicles_fuel.id
       LEFT JOIN vehicles_transmissions
         ON vehicles.transmission_id = vehicles_transmissions.id
       LEFT JOIN vehicles_axles
         ON vehicles.axle_id = vehicles_axles.id
       LEFT JOIN vehicles_sub_years
         ON vehicles.sub_year_id = vehicles_sub_years.id
       INNER JOIN members
         ON vehicles.member_id = members.id
       LEFT JOIN vehicles_categories
         ON vehicles.category_id = vehicles_categories.id
WHERE  vehicles.status = 1
       AND vehicles.date_from < 1330349235
       AND vehicles.date_to > 1330349235
       AND vehicles.type_id = 1
       AND ( vehicles.price >= 0
             AND vehicles.price <= 1000000 )

Вот схема таблиц транспортных средств:

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number_plate` varchar(100) NOT NULL,
  `type_id` int(11) NOT NULL,
  `make_id` int(11) NOT NULL,
  `model_id` int(11) NOT NULL,
  `model_sub_type` varchar(250) NOT NULL,
  `engine_size` decimal(12,1) NOT NULL,
  `vehicle_year` int(11) NOT NULL,
  `sub_year_id` int(11) NOT NULL,
  `mileage` int(11) NOT NULL,
  `fuel_id` int(11) NOT NULL,
  `transmission_id` int(11) NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `trade_or_private` tinyint(4) NOT NULL,
  `postcode` varchar(25) NOT NULL,
  `gps_lat` varchar(50) NOT NULL,
  `gps_lon` varchar(50) NOT NULL,
  `img1` varchar(100) NOT NULL,
  `img2` varchar(100) NOT NULL,
  `img3` varchar(100) NOT NULL,
  `img4` varchar(100) NOT NULL,
  `img5` varchar(100) NOT NULL,
  `img6` varchar(100) NOT NULL,
  `img7` varchar(100) NOT NULL,
  `img8` varchar(100) NOT NULL,
  `img9` varchar(100) NOT NULL,
  `img10` varchar(100) NOT NULL,
  `is_featured` tinyint(4) NOT NULL,
  `body_id` int(11) NOT NULL,
  `colour_id` int(11) NOT NULL,
  `doors` tinyint(4) NOT NULL,
  `axle_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `contents` text NOT NULL,
  `date_created` int(11) NOT NULL,
  `date_edited` int(11) NOT NULL,
  `date_from` int(11) NOT NULL,
  `date_to` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `inactive_id` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  KEY `make_id` (`make_id`),
  KEY `model_id` (`model_id`),
  KEY `fuel_id` (`fuel_id`),
  KEY `transmission_id` (`transmission_id`),
  KEY `body_id` (`body_id`),
  KEY `colour_id` (`colour_id`),
  KEY `axle_id` (`axle_id`),
  KEY `category_id` (`category_id`),
  KEY `vehicle_year` (`vehicle_year`),
  KEY `mileage` (`mileage`),
  KEY `status` (`status`),
  KEY `date_from` (`date_from`),
  KEY `date_to` (`date_to`),
  KEY `trade_or_private` (`trade_or_private`),
  KEY `doors` (`doors`),
  KEY `price` (`price`),
  KEY `engine_size` (`engine_size`),
  KEY `sub_year_id` (`sub_year_id`),
  KEY `member_id` (`member_id`),
  KEY `date_created` (`date_created`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=136237 ;

EXPLAIN:

1   SIMPLE  vehicles    ref     type_id,make_id,status,date_from,date_to,price,mem...   type_id     4   const   85695   Using where
1   SIMPLE  members     index   PRIMARY     PRIMARY     4   NULL    3   Using where; Using index; Using join buffer
1   SIMPLE  vehicles_makes  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.make_id    1   Using index
1   SIMPLE  vehicles_models     eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.model_id   1   Using index
1   SIMPLE  vehicles_fuel   eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.fuel_id    1   Using index
1   SIMPLE  vehicles_transmissions  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.transmission_id    1   Using index
1   SIMPLE  vehicles_axles  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.axle_id    1   Using index
1   SIMPLE  vehicles_sub_years  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.sub_year_id    1   Using index
1   SIMPLE  vehicles_categories     eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.category_id    1   Using index

Ответы

Ответ 1

Улучшение предложения WHERE

Ваш EXPLAIN показывает, что MySQL использует только один индекс (type_id) для выбора строк, которые соответствуют предложению WHERE, даже если в предложении есть несколько критериев.

Чтобы иметь возможность использовать индекс для всех критериев в предложении WHERE и как можно быстрее уменьшить размер набора результатов, добавьте индекс нескольких столбцов в следующие столбцы таблицы транспортных средств:

(status, date_from, date_to, type_id, price)

Столбцы должны быть в порядке наивысшей мощности по меньшей мере.

Например, vehicles.date_from, скорее всего, имеет более четкие значения, чем status, поэтому поставьте столбец date_from до status, например:

(date_from, date_to, price, type_id, status)

Это должно уменьшить строки, возвращаемые в первой части выполнения запроса, и должно быть продемонстрировано с более низким количеством строк в первой строке результата EXPLAIN.

Вы также заметите, что MySQL будет использовать индекс с несколькими столбцами для WHERE в результате EXPLAIN. Если, случайно, это не так, вы должны намекать или заставлять индекс с несколькими столбцами.

Удаление ненужных JOINs

Не похоже, что вы используете какие-либо поля в любой из объединенных таблиц, поэтому удалите соединения. Это приведет к удалению всей дополнительной работы запроса и приведет вас к одному простому плану выполнения (одна строка в результате EXPLAIN).

Каждая таблица JOINed вызывает дополнительный поиск в строке набора результатов. Итак, если предложение WHERE выбирает 5000 строк из транспортных средств, так как у вас есть 8 соединений на транспортных средствах, у вас будет 5 000 * 8 = 40 000 запросов. Это много, чтобы спросить у вашего сервера базы данных.

Ответ 2

Вместо дорогостоящего вычисления точного расстояния для всех строк используйте ограничивающий прямоугольник и вычисляйте точное расстояние только для строк внутри поля.

Самый простой возможный пример - рассчитать минимальную/максимальную долготу и широту, которые вас интересуют, и добавить ее в предложение WHERE. Таким образом, расстояние будет рассчитываться только для подмножества строк.

WHERE
    vehicles.gps_lat > min_lat ANDd vehicles.gps_lat < max_lat AND
    vehicles.gps_lon > min_lon AND vehicles.gps_lon < max_lon

Для более сложных решений см.

Ответ 3

Является ли ваш SQL быстрее без этого?

Round(3959 * Acos(Cos(Radians(51.465436)) *
  Cos(Radians(vehicles.gps_lat)) *
  Cos(Radians(vehicles.gps_lon) - 
  Radians(-0.296482)) + 
  Sin(Radians(51.465436)) * 
  Sin(Radians(vehicles.gps_lat)))) AS distance

выполнение математического уравнения очень дорого

Возможно, вам стоит подумать о материализованном представлении, которое предварительно вычисляет расстояние, и вы можете выбрать из этого представления. В зависимости от того, насколько динамичны ваши данные, вам может не потребоваться слишком часто обновлять данные.

Ответ 4

У вас также есть индексы на них:

vehicles.status
vehicles.date_from
vehicles.date_to
vehicles.type_id
vehicles.price

Ответ 5

Чтобы быть немного более конкретным, чем @Randy индексов, я считаю, что его намерение состояло в том, чтобы иметь индекс COMPOUND, чтобы использовать ваши критерии запроса... Один индекс, построенный на MINIMUM...

( status, type_id, date_from )

но может быть расширен, чтобы включить date_to и цену, но не знаю, насколько индекс на этом гранулированном уровне может действительно помочь

( status, type_id, date_from, date_to, price )

EDIT за комментарии

Вам не нужны все эти индивидуальные индексы... Да, первичный ключ сам по себе. Тем не менее, для других, у вас должны быть составные индексы, основанные на ваших общих критериях запроса и удалении других... двигатель может запутаться, что может быть лучше всего подходит для запроса. Если вы знаете, что всегда ищете определенный статус, тип и дату (при поиске автомобилей), сделайте это как один индекс. Если запрос ищет такую информацию, но и цены в рамках этих критериев, он уже будет очень близок к нескольким индексированным записям, которые квалифицируются и пролетают по цене как дополнительные критерии.

Если вы предлагаете такие запросы, как "Только автоматическая" или "Ручная", независимо от года/периода, тогда да, это может быть собственный индекс. Однако, если вы TYPICALLY имеете некоторые другие "общие" критерии, примените это как вторичное, которое МОЖЕТ быть использовано в запросе. Пример: если вы ищете Ручные трансмиссии, которые являются 2-дверными и 4-дверными, укажите свой индекс (transmission_id, category_id).

Опять же, вы хотите все, что поможет сузить поле критериев, основанное на некотором "минимальном" условии. Если вы ссылаетесь на дополнительный столбец на индекс, который может быть "обычно", это должно только помочь производительности.