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

[berkut]

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

подсобите камрады, что-то не понимаю, почему идет полный скан таблицы и не юзаются индексы:

CREATE TABLE users_associations(
  id INT (11) UNSIGNED NOT NULL DEFAULT 0,
  item_1 MEDIUMINT (9) UNSIGNED NOT NULL DEFAULT 0,
  item_2 MEDIUMINT (9) UNSIGNED NOT NULL DEFAULT 0,
  creator MEDIUMINT (9) UNSIGNED NOT NULL DEFAULT 0,
  flag SET ('own', 'friend') DEFAULT NULL,
  INDEX creator USING BTREE (creator),
  INDEX creator_2 USING BTREE (creator, id),
  UNIQUE INDEX index1 USING BTREE (creator, item_1, item_2),
  INDEX index2 USING BTREE (item_1, item_2)
)
ENGINE = MYISAM
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE user_association_data(
  id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
  association_id INT (11) UNSIGNED NOT NULL DEFAULT 0,
  `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  description TEXT NOT NULL,
  show_to ENUM ('all', 'myself', 'friends') DEFAULT 'all',
  PRIMARY KEY (id),
  INDEX assId USING BTREE (association_id, `date`)
)
ENGINE = MYISAM
AUTO_INCREMENT = 66854
CHARACTER SET utf8
COLLATE utf8_general_ci;

в первой таблице 133000 рядов, во второй - 66000

SELECT SQL_CALC_FOUND_ROWS *
  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, 10

эксплейн говорит:
SIMPLE, table-uad, type-all, key-null, rows-66626, extra-using filesort
запрос исполняется 2 секунды

 

[Mr_Max]

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
..............
In some cases, MySQL cannot use indexes to resolve the ORDER BY
..............

You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

 

[berkut]

Mr_Max
таблицы как видно 2. первая самая таблица в эксплейне - uad(а не как в запросе - ua). поэтому, непойму как эта цитата относится к ситуации.. force index - ноль эффекта на эксплейн. STRAIGHT_JOIN -

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ua	ref	index1,creator,creator_2	creator_2	3	const	110927	"Using temporary; Using filesort"
1	SIMPLE	uad	ref	assId	assId	4	ua.id	1

 

[PILOT]

Наличие ключей (creator, id) и (association_id, `date`) в двух таблицах не даёт ключа (`creator`,`date`) в объединённой

Что возвращает select count(*) from users_associations where creator = 2? Подозреваю что 100к. Если не так, то можно бороться через derived, сократив число рядов таблицы для сортировки до результата селекта.

 

[berkut]

PILOT
записей с creator=2 113000. А как добиться (`creator`,`date`) в объединённой? пробывал на date ставить отдельный индекс - ноль эффекта. кстати методом тыка обнаружил, что тормозит какбЭ SQL_CALC_FOUND_ROWS - его убираю - время выполнения с 2.5 сек падает до 0,09. в итоге пока сделал в 2 запроса, без calc_found_rows - 0,09 + 0,3(тот-же запрос с COUNT(*))

 

[PILOT]

Автор оригинала: berkut
А как добиться (`creator`,`date`) в объединённой?

Никак. Дерево индексов уже построено. После объединения оно естественно не достраивается. Надо архитектуру менять немного чтобы нужные поля были в одной таблице.

 

[prolis]

Я бы на месте сервера то-же бы не понял, что хотят-то:
[sql]
SELECT SQL_CALC_FOUND_ROWS *
ORDER BY uad.date DESC
LIMIT 0, 10
[/sql]
- либо отсортировать и по индексу взять 10 строк, либо и отсортировать, и все посчитать. При count(*) он более уверен что надо.

 

[dr-sm]

Автор оригинала: berkut
PILOT
записей с creator=2 113000. А как добиться (`creator`,`date`) в объединённой? пробывал на date ставить отдельный индекс - ноль эффекта. кстати методом тыка обнаружил, что тормозит какбЭ SQL_CALC_FOUND_ROWS - его убираю - время выполнения с 2.5 сек падает до 0,09. в итоге пока сделал в 2 запроса, без calc_found_rows - 0,09 + 0,3(тот-же запрос с COUNT(*))

так кагбэ баг там:
http://bugs.mysql.com/bug.php?id=18454