Форум пользователей MySQL

backflip · 13.05.2009 17:20:54

Здравствуйте.

Есть проблема с выборкой данных, надеюсь, на вашу помощь.
Имеются две таблицы: одна содержит альбомы, вторая - исполнителей. Необходимо сделать выборку связав две таблицы альбом+исполнитель. Нет проблем, пока не начинаю сортировать по названию исполнителя (без этого - никак), но при этом время выполнения запроса стремиться к бесконечности.

Обе таблицы содержат более 2ух миллионов записей, LIMIT использую.

CREATE TABLE IF NOT EXISTS `music_albums` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `singer_id` int(10) unsigned NOT NULL default '0',

  `rating` float NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `music_singer_names` (

  `id` int(10) unsigned NOT NULL,

  `singer_name_en` varchar(255) NOT NULL,

  `singer_name_ru` varchar(255) NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `singer_name_en` (`singer_name_en`),

  KEY `singer_name_ru` (`singer_name_ru`)

) ENGINE=MyISAM;

SQL:

SELECT * FROM `music_albums` LEFT JOIN `music_singer_names` ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;

 

explain:

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

| id | select_type | table              | type   | possible_keys | key     | key_len | ref                            | rows    | Extra                           |

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

|  1 | SIMPLE      | music_albums       | ALL    | NULL          | NULL    | NULL    | NULL                           | 2591774 | Using temporary; Using filesort |

|  1 | SIMPLE      | music_singer_names | eq_ref | PRIMARY       | PRIMARY | 4       | music_albums.singer_id         |       1 |                                 |

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

paulus · 13.05.2009 18:23:43

Ну, во-первых, Вам нужен ключик на music_albums(singer_id), раз уж он участвует в объединении.
Во-вторых, наверняка запрос более длинный

backflip · 13.05.2009 19:08:01

Спасибо за ответ.

Конечно в итоге запрос еще больше будет - сейчас хочется его заставить работать хотя бы с одним объединением.

paulus написал:
Вам нужен ключик на music_albums(singer_id), раз уж он участвует в объединении.

Не могли бы вы объяснить какой ключ имеется в виду?

backflip · 14.05.2009 10:20:47

Проблема, судя по всему, в размере временной таблицы - результате объединения. Когда попробовал создавать временные таблицы в оперативной памяти

mount tmpfs /tmp/mysql -t tmpfs

Mysql создат таблицу > 4Гб, а потом ее сортирует. Думаю, что нужно как то оптимизировать запрос или структуру, чтобы не объединять такое количество строк.
Но вот решение - на ум не приходит.

paulus · 14.05.2009 15:48:18

Я имею в виду ключик, получающийся командой
ALTER TABLE music_albums ADD INDEX(singer_id)

Magz · 14.05.2009 15:54:34

Попробуйте для начала так:

SELECT * FROM `music_albums`LEFT JOIN (SELECT * `music_singer_names` ORDER BY `singer_name_en` LIMIT 0,50) msn ON (`msn`.`id`=`music_albums`.`singer_id`)

В этом случае в объединении будут участвовать только 50 выбранных записей. Да и сортировать нужно будет меньший объем.

Отредактированно Magz (14.05.2009 15:54:51)

paulus · 14.05.2009 16:54:11

Это все равно даст FTS, правда ведь? ключика то нет.

backflip · 14.05.2009 19:04:48

paulus, к сожалению легче не стало. Запрос все равно выполняется долго (убил его через 5 минут).

Magz, да я тоже думал о подобном. Но можно ли тогда будет указывать дополнительные условия, например `rating` > 0 ? Или придется делать либо то, либо то?
Кстати, ошибку синтекса на ваш запрос выдает.

paulus · 14.05.2009 20:18:23

А explain?

backflip · 15.05.2009 10:21:36

paulus написал:
А explain?

Без изменений.

Magz · 15.05.2009 11:56:53

paulus написал:
Это все равно даст FTS, правда ведь? ключика то нет.

Чистая правда! Без ключика будет гораздо хуже, чем с ним. Просто я думаю, что backflip уже последовал Вашему совету, поэтому ничего про ключик не сказал.

backflip написал:
Magz, да я тоже думал о подобном. Но можно ли тогда будет указывать дополнительные условия, например `rating` > 0 ? Или придется делать либо то, либо то? Кстати, ошибку синтекса на ваш запрос выдает.

Если стоит задача "Выбрать исполнителей в алфавитном порядке рейтинг альбомов которых не равен нулю", то "в лоб" не пройдет. Нужно будет идти от обратного - сначала выбрать все альбомы с ненулевым рейтингом, а потом их свзяывать с исполнителями. Основная идея - это сократить количество строк после объединения. Ибо никакие индексы не помогут, когда нужно отсортировать 4 ГБ данных!

Какая конкретно ошибка синаксиса вылезает?

backflip · 15.05.2009 12:44:42

В принципе понял, что либо то либо то В крайнем случае оставлю пользователей без сортировки и заменю ее фильтрами.

Magz написал:
Какая конкретно ошибка синаксиса вылезает?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`music_singer_names` ORDER BY `singer_name_en` LIMIT 0 , 50 ) msn ON ( `msn` . `' at line 1

Отредактированно backflip (15.05.2009 12:45:13)

paulus · 15.05.2009 14:02:21

Рррр, пришлось создать таблички

Код:

[aquatica] root test > explain SELECT * FROM `music_albums` LEFT JOIN `music_singer_names` ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref                         | rows  | Extra                           |
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
|  1 | SIMPLE      | music_albums       | ALL    | NULL          | NULL    | NULL    | NULL                        | 32768 | Using temporary; Using filesort | 
|  1 | SIMPLE      | music_singer_names | eq_ref | PRIMARY       | PRIMARY | 4       | test.music_albums.singer_id |     1 |                                 | 
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
2 rows in set (0,00 sec)

[aquatica] root test > alter table music_albums add index (singer_id);
Query OK, 32768 rows affected (0,15 sec)
Records: 32768  Duplicates: 0  Warnings: 0

[aquatica] root test > explain SELECT * FROM `music_albums` LEFT JOIN `music_singer_names` ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref                         | rows  | Extra                           |
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
|  1 | SIMPLE      | music_albums       | ALL    | NULL          | NULL    | NULL    | NULL                        | 32768 | Using temporary; Using filesort | 
|  1 | SIMPLE      | music_singer_names | eq_ref | PRIMARY       | PRIMARY | 4       | test.music_albums.singer_id |     1 |                                 | 
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+
2 rows in set (0,00 sec)

[aquatica] root test > explain SELECT * FROM `music_albums` JOIN `music_singer_names` ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;
+----+-------------+--------------------+-------+---------------+----------------+---------+----------------------------+------+-------------+
| id | select_type | table              | type  | possible_keys | key            | key_len | ref                        | rows | Extra       |
+----+-------------+--------------------+-------+---------------+----------------+---------+----------------------------+------+-------------+
|  1 | SIMPLE      | music_singer_names | index | PRIMARY       | singer_name_en | 257     | NULL                       |   50 |             | 
|  1 | SIMPLE      | music_albums       | ref   | singer_id     | singer_id      | 4       | test.music_singer_names.id |    1 | Using where | 
+----+-------------+--------------------+-------+---------------+----------------+---------+----------------------------+------+-------------+
2 rows in set (0,00 sec)

Учитывая сортировку, левое объединение можно спокойно отбросить

Magz · 15.05.2009 14:33:18

backflip написал:
В принципе понял, что либо то либо то В крайнем случае оставлю пользователей без сортировки и заменю ее фильтрами.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`music_singer_names` ORDER BY `singer_name_en` LIMIT 0 , 50 ) msn ON ( `msn` . `' at line 1

Забыл слово FROM во вложенном запорсе. Правильный вариант:

SELECT * FROM `music_albums` LEFT JOIN (SELECT * FROM `music_singer_names` ORDER BY `singer_name_en` LIMIT 0,50) msn ON (`msn`.`id`=`music_albums`.`singer_id`)

paulus написал:
Query OK, 32768 rows affected

А как Вы сделали 32 тысячи строчек? Я для таких веще пишу скрипт на php, но чувствую, есть способ проще

backflip · 15.05.2009 16:02:17

paulus,

Выполняю:

explain SELECT * FROM `music_albums` JOIN `music_singer_names` ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

| id | select_type | table              | type   | possible_keys | key     | key_len | ref                            | rows    | Extra                           |

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

|  1 | SIMPLE      | music_albums       | ALL    | singer_id     | NULL    | NULL    | NULL                           | 2591774 | Using temporary; Using filesort |

|  1 | SIMPLE      | music_singer_names | eq_ref | PRIMARY       | PRIMARY | 4       | music_albums.singer_id |       1 |                                 |

+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------+---------+---------------------------------+

как у вас не получается.

И ключи вроде все на месте:

mysql> show index from music_albums;

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| music_albums |          0 | PRIMARY   |            1 | id          | A         |     2591774 |     NULL | NULL   |      | BTREE      |         |

| music_albums |          1 | singer_id |            1 | singer_id   | A         |      647943 |     NULL | NULL   |      | BTREE      |         |

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> show index from music_singer_names;

+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

| Table              | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

| music_singer_names |          0 | PRIMARY        |            1 | id             | A         |     2141661 |     NULL | NULL   |      | BTREE      |         |

| music_singer_names |          1 | singer_name_en |            1 | singer_name_en | A         |     2141661 |     NULL | NULL   |      | BTREE      |         |

| music_singer_names |          1 | singer_name_ru |            1 | singer_name_ru | A         |           1 |     NULL | NULL   |      | BTREE      |         |

+--------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

Отредактированно backflip (15.05.2009 16:23:36)

paulus · 15.05.2009 17:02:58

Хммм... у меня данных, конечно, поменьше.
Давайте попробуем помочь оптимизатору

SELECT SQL_STRAIGHT_JOIN * FROM music_singer_names JOIN music_albums ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;

backflip · 15.05.2009 17:27:56

paulus написал:
SELECT SQL_STRAIGHT_JOIN * FROM music_singer_names JOIN music_albums ON (`music_singer_names`.`id`=`music_albums`.`singer_id`) ORDER BY `singer_name_en` LIMIT 0,50;

Огромнейшее спасибо. STRAIGHT_JOIN помог - все работает как надо.
Думаю, проблема решена.

Leema · 30.06.2009 13:20:12

Добрый день. Немного не в тему, но как это всё через echo вывести? Типа

Музыкант 1
Альбом 1
Альбом 2
...............
Музыкант 2
Альбом 1
Альбом 2

Надо использовать вложенный цикл на альбомы? Может у кого есть пример подобного кода, второй день бьюсь, выводится всё совершенно не так, как хотелось бы.

paulus · 03.07.2009 00:26:13

echo 'Музыкант 1<br>Альбом1<br>...'

Отдельную тему сделайте со структурой базы — тогда получите нормальный ответ

Форум пользователей MySQL

#1 13.05.2009 17:20:54

JOIN + сортировка

#2 13.05.2009 18:23:43

Re: JOIN + сортировка

#3 13.05.2009 19:08:01

Re: JOIN + сортировка

paulus написал:

#4 14.05.2009 10:20:47

Re: JOIN + сортировка

#5 14.05.2009 15:48:18

Re: JOIN + сортировка

#6 14.05.2009 15:54:34

Re: JOIN + сортировка

#7 14.05.2009 16:54:11

Re: JOIN + сортировка

#8 14.05.2009 19:04:48

Re: JOIN + сортировка

#9 14.05.2009 20:18:23

Re: JOIN + сортировка

#10 15.05.2009 10:21:36

Re: JOIN + сортировка

paulus написал:

#11 15.05.2009 11:56:53

Re: JOIN + сортировка

paulus написал:

backflip написал:

#12 15.05.2009 12:44:42

Re: JOIN + сортировка

Magz написал:

#13 15.05.2009 14:02:21

Re: JOIN + сортировка

Код:

#14 15.05.2009 14:33:18

Re: JOIN + сортировка

backflip написал:

paulus написал:

#15 15.05.2009 16:02:17

Re: JOIN + сортировка

#16 15.05.2009 17:02:58

Re: JOIN + сортировка

#17 15.05.2009 17:27:56

Re: JOIN + сортировка

paulus написал:

#18 30.06.2009 13:20:12

Re: JOIN + сортировка

#19 03.07.2009 00:26:13

Re: JOIN + сортировка

Board footer