LINUX.ORG.RU

MySQL запрос, проблема с использованием индексов!

 , ,


0

1

1 таблица с именами автомобилей, а 2ая с именами марок авто table1 таблица - у неё есть следующие поля:

Чтобы было яснее, это будет таблица с мобильными телефонами.

table1:
l_id      - идентификатор автомобиля, он уникален
l_name    - наименование авто («ВАЗ 2101»,«AUDI R8»)
a_id      - id марки автомобиля, cвязь с 2ой таблицей
--------------------------------------------------------------------- 
table2:
a_id      - уникальный id марки авто
g_id      - id страны производителя авто (от 1 до 100)

Очень сложная структура таблиц, поэтому прошу помощи.

Делаем сложный запрос:

SELECT
   SQL_NO_CACHE #для чистоты эксперемента
   * 
FROM 
   table1 as al,
   table2 as a
WHERE
   a.a_id=al.a_id
AND
   g_id IN (1)

limit 1000 , 30;

Получается мы фильтруем всех производителей кроме страны с id 1, запрос выполняется 0.0003 сек.

Попробуем вывести 20 стран: g_id IN (1,2,3,4,5,6,7,8,9,20,30,40,50,60,70,80,90,99,55,77). Всё отлично, всё так же быстро.

Делаем сортировку по имени авто: ORDER BY l_name ASC - и вместо 0.0003 сек получаем 2 секунды. По-моему долго!

Explain подсказал, что l_name переберается и не использует индекс! Нам поможет FORCE INDEX (l_name). О чудо! 0.0400 сек! По-моему для связывания таблиц, установки WHERE и сортировки - 0.0400 сек это быстро.

Казалось бы выход найден. Используем индексы. Вернемся к запросу в котором мы фильтровали все страны кроме одной g_id in (1). Если опять это выполнить с использованием уже ускоряющего нас FORCE INDEX (l_name) - время выполнения будет 1 сек. КАК ТАК?? Кажется нелогичным, уменьшить объем выбираемой и сортируемой информации и при этом возрастает время обработки в 20 раз. Профилирование запроса покажет, что 99.5% времени процесс в статусе sending data. Добавляя в условие g_id новые страны дополнительного фильтрования - время sending data снижается по мере добавления идентификаторов стран в условие. Если не использовать FORCE INDEX (l_name), то скорость выполнения с условием g_id in (1) быстрая.

Неужели мне придется использовать FORCE INDEX (l_name) в зависимости от разности количества записей условия? :)

Буду рад любым пинкам в правильном направлении.

★★

Последнее исправление: ex3me (всего исправлений: 3)

Буду рад любым пинкам в правильном направлении.

left join

А вообще

show create table `table1`;

и

show create table `table2`;

В студию

А то нихера не понятно на каких полях у тебя индексы. Мож ты на поле сортировки его поставил, а на поля по которым связываешь - нет.

r_asian ★☆☆
()
Ответ на: комментарий от r_asian
CREATE TABLE `table1` (
  `l_id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) NOT NULL,
  `l_name` varchar(256) NOT NULL,
  PRIMARY KEY (`l_id`),
  KEY `a_id` (`a_id`),
  KEY `l_name` (`l_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1222617 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `table2` (
  `a_id` int(11) NOT NULL AUTO_INCREMENT,
  `g_id` int(11) NOT NULL,
  `y_id` int(11) NOT NULL,
  PRIMARY KEY (`a_id`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
ex3me ★★
() автор топика
Ответ на: комментарий от Apple-ch

Дело в том, что данных много - table1 более 1 млн записей, table2 - 100000 записей.

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

ex3me ★★
() автор топика
Ответ на: комментарий от ex3me

1. Добавь индекс для `g_id`, у тебя же это поле в условии стоит

2. Давай сделаем с твоим запросом вот что

SELECT
   SQL_NO_CACHE #для чистоты эксперемента
   `al`.`i_id` as `id`,
   `al`.`l_name` as `name`,
   `a`.`a_id` as `a_id`
FROM 
   `table1` as `al`
      LEFT JOIN
   `table2` as `a`
      ON `a`.`a_id`=`al`.`a_id`
WHERE
   `a`.`g_id` IN (1)
ORDER BY 
   a.l_name ASC
limit 1000 , 30;

То есть

а) Свяжем таблицы через JOIN

б) Ограничим число выбираемых полей

r_asian ★☆☆
()
Ответ на: комментарий от ex3me

Поставил. Тот же запрос который выполняется 1 сек. выполняться стал 1 min 3.64 sec

гм.. гм.. а ты не юзаешь там force index? если юзаешь, убери.

ну и эксплейн в студию если совет разина не поможет.

AndreyKl ★★★★★
()
Ответ на: комментарий от r_asian

2. Запрос выполнил. Запрос выполняется ровно столько, сколько и мой запрос. (я уже задавал вопрос на другом сайте, мне дали подобный запрос в ответ)

1. Как только я добавил индекс, Запрос выполнился за 0.05 сек. Но как только я в условие IN добавил десяток циферок - запрос стал выполняться больше секунды. Проблема стала «с точностью до наоборот» :)

б. Запрос , таблица, имена полей - тестовые. Поэтому я использую «*» вместо перечисления. Перечисление никакого ускорения не даст, т.к. проблема совсем в другом.

ex3me ★★
() автор топика
Ответ на: комментарий от VirRaa

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

ex3me ★★
() автор топика
Ответ на: комментарий от ex3me

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

Я щаслив за тебя.

VirRaa ★★★
()

что-то мне кажется что во второй таблице нужно не два отдельных индекса а один составной

bismi
()
Ответ на: комментарий от bismi

делал... причем составной так же работает как и 2 отдельных индекса

ex3me ★★
() автор топика
Ответ на: комментарий от ex3me

лимит можно использовать но смещение на больших таблицах никогда, посмотри в плане запроса как мускул его обрабатывает - чтоб найти смещение мускуль пройдется по всем записям игнорируя индекс (а как по другому?), поэтому если будешь брать записи с 100000 20 штук то физически мускуль отгребет 100000+20 записей. http://www.scribd.com/doc/14683263/Efficient-Pagination-Using-MySQL

RR
()
Ответ на: комментарий от bismi

Mysql обычно делает слияние довольно индексов довольно в тему.. так что вряд ли поможет..

AndreyKl ★★★★★
()
Ответ на: комментарий от r_asian

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

baverman ★★★
()
Ответ на: комментарий от baverman

postgres the best. для холивара создавайте отдельный тред.

На это сообщение отвечать не нужно.

ex3me ★★
() автор топика
Ответ на: комментарий от ex3me

Под эту задачу неплохо сфинкс подходит - http://habrahabr.ru/blogs/webdev/40443/ Еще можно заюзать какой-то хандлерсокет или мемкашединнодб интерфейс, ну и перейти на иннодб - муисам сакс.

RR
()
Ответ на: комментарий от baverman

Мне ченжлог новой постгри понравился)

RR
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.