Меню
Что нового?

оптимизация order by

berkut

Новичок
оптимизация order by

подскажите как избавиться от using temp, file sort на таком запросе:
Код: 
SELECT *
FROM users_associations AS ua
     INNER JOIN user_association_data AS uad ON ua.id = uad.association_id     
WHERE ua.creator = 2
ORDER BY uad.`date` DESC
LIMIT 0, 4
Код: 
id  select_type  table   type  possible_keys  key      key_len  ref     rows   Extra                                                                                                
1 SIMPLE          ua     ref    creator,ghj   creator    3      const   3_449  Using temporary; Using filesort                                                                      
1 SIMPLE          uad    ref    assId         assId      4      ua.id _ 1
таблицы:
Код: 
CREATE TABLE `users_associations` (
  `id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
  `creator` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
  `flag` SET('attending','friend') DEFAULT NULL,
  KEY `creator` (`creator`),
  KEY `ghj` (`creator`, `id`)
)ENGINE=MyISAM
ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `user_association_data` (
  `id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `association_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
  `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `description` TEXT COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `assId` (`association_id`)

)ENGINE=MyISAM
AUTO_INCREMENT=10147 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
-~{}~ 18.03.09 15:14:

я пробывал создать отдельно индекс на date. составной: uad.association_id, date - толку ноль

-~{}~ 18.03.09 15:19:

собсно я вообще не понимаю, почему это на таблице, по которой и не сортируется ничего. если убрать order by - temp, filesort пропадает

-~{}~ 18.03.09 15:23:

я даже уже и проблему решил, почти случайно, но почему он в этом варианте юзает временную на ua???
 

berkut

Новичок
zerkms
а я хз... в мане вроде нет упоминания о where & order...
вообщем занимательно это.
Код: 
SELECT *
FROM user_association_data AS uad 
     STRAIGHT_JOIN users_associations AS ua ON ua.id = uad.association_id     
WHERE ua.creator = 2
ORDER BY uad.`date` DESC
LIMIT 0, 4
ушло filesort & temporary. в extra все пусто. производительность увеличилась почти в 3 раза. но это тест - в реальном запросе к ним джоинится еще 5 таблиц - там производительность с 560мс выросла до 46мс
 

berkut

Новичок
когда искал наткнулся на эту статью;)

-~{}~ 18.03.09 15:38:

whirlwind
это как и зачем вообще может? ну выборка такая-же, и то что нужно, значит не после

-~{}~ 18.03.09 15:41:

а ну есчо я индекс поменял:
KEY `assId` (`association_id`, `date`). но без straight join он не используется
 
Wicked

Wicked

Новичок
может расскажешь, что за задача-то? зачем нужен этот запрос?
 
whirlwind

whirlwind

TDD infected, paranoid
кстате у меня такая же хрень тока нифига не убирается

Код: 
 EXPLAIN SELECT SUM(h.amount),t.name FROM mps_balance_hold AS h STRAIGHT_JOIN mps_terminal AS t ON (h.terminal = t.id)
 WHERE h.merchant = 1 GROUP BY t.name LIMIT 4;
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys     | key      | key_len | ref   | rows | Extra                           |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------+
|  1 | SIMPLE      | h     | ref  | merchant,terminal | merchant | 8       | const |   63 | Using temporary; Using filesort | 
|  1 | SIMPLE      | t     | ALL  | PRIMARY           | NULL     | NULL    | NULL  |    3 | Using where                     | 
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------+
2 rows in set (0.00 sec)
-~{}~ 18.03.09 15:31:

или из-за групы?
 
MuXaJIbI41981

MuXaJIbI41981

Новичок
whirlwind это из другой оперы но так на будущее ... если есть GROUP BY то даже если не надо ничего сортировать то лучше все равно писать в добавок ORDER BY NULL
 
fixxxer

fixxxer

К.О.
Партнер клуба
> а я хз... в мане вроде нет упоминания о where & order...

вот представь себе записную книжку с вырезанными "уголками", на которых написаны первые буквы фамилий, а на самих листках номера телефонов.

если тебе надо взять например все, у кого фамилии начинаются с букв от "Б" до "И" в алфавитном порядке, ты просто вытащищь одним действием соответствующие листки, то есть используешь индекс.

а вот теперь тебе надо еще и отсортировать их по номеру телефона. как ты это сделаешь, не разложив все отдельно и не переложив? никак. вот и filesort.

теперь если в выборке будут всего три например телефона, ты в состоянии их разложить фактически сразу, прямо держа все в руках - потому что смог все запомнить - три листка поместились в твои sort buffers. а вот если их 30, то ты разложишь их все на стол и будешь двигать - это уже в sort buffers не влезли и получаем "настоящий" filesort (в mysql filesort назван не совсем корректно, в случае если помещается в sort buffers то это хоть и пишется filesort но на самом деле - memory sort).
 
whirlwind

whirlwind

TDD infected, paranoid
Автор оригинала: MuXaJIbI41981
Нажмите для раскрытия...
Ничего из предложенного не помогло. Только c force index чуток ускорил. Ну да фик с ним это мне не критичное просто хотел посмотреть аналогичные запросы.
 
fixxxer

fixxxer

К.О.
Партнер клуба
ну кстати оптимизатор вполне может оказаться умнее форс индекса: в зависимости от количества строк он может вполне разумно определить, что отсортировать будет быстрее чем делать fullscan таблицы, например. но поскольку в mysql оно rule based, а не cost based, то бывает и не умнее =)
 

berkut

Новичок
fixxxer ну это все здорово... только filesort & temp исчезли на финальном запросе-то
 
zerkms

zerkms

TDD infected
Команда форума
berkut
они исчезли (и появлялись) только в этой ситуации. не исключено, что на других объёмах данных всё будет иначе.
 
Войдите или зарегистрируйтесь для ответа.
Сверху