нужна простая выборка из 2-х больших таблиц

[KillDead]

Здравствуйте. вроде бы простейшая задача- есть 2 таблицы
1 product (id, name, rating)
2 category_connect (id, product_id)
продукт может иметь более 1-й категории. Нужно выбрать продукты с опр категорией и чтобы можно было сортировать по рейтингу. Всё. Но дело осложняется тем, что продуктов порядка миллиона. делаю
select * from product left join cat On cat.product_id = product.id Where cat.id= 666
но это медленно работает. Можно переписать, выбрав вначале ИД продуктов из таблицы категории а потом через список IN найти в таблице продуктов, но тогда сортировка по рейтингу невозможна.

Как быть?

 

[BDSG]

на cat.id, cat.product_id, product.id индексы стоят?.. какая субд?.. именно выбирается долго, или приложением выборка долго обрабатывается?..

схемы обоих таблиц и explain запроса предоставьте, пожалуйста..

 

[KillDead]

да, извиняюсь, слишком мало вводных дал
база - mysql
таблицы

`genre_connect` (
  `film_id` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  KEY `film_id` (`film_id`),
  KEY `genre_id` (`genre_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

`films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `price`int(5) NOT NULL,
  `rating` varchar(100) NOT NULL,
... ещё 15 полей

   `genre_ids` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `short_story` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251

SQL-запрос: EXPLAIn SELECT SQL_CALC_FOUND_ROWS f.* FROM genre_connect ci LEFT JOIN films f ON ci.film_id = f.id WHERE ci.genre_id=5 limit 200, 50;
Строки: 2



+----+-------------+-------+--------+---------------+----------+---------+----------------------------------------+--------+-------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                                    | rows   | Extra |
+----+-------------+-------+--------+---------------+----------+---------+----------------------------------------+--------+-------+
|  1 | SIMPLE      | ci    | ref    | genre_id      | genre_id | 4       | const                                  | 145869 |  Using where; Using temporary; Using filesort     |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY  | 4       | shop.ci.film_id                |      1 |       |
+----+-------------+-------+--------+---------------+----------+---------+----------------------------------------+--------+-------+

выборка по данному запросу идёт порядка 0,7 секунд. Но при нагрузочном тестировании эта хрень напрочь тормозит работу.

 

[BDSG]

судя по схеме, genre_connect.genre_id, genre_connect.film_idу вас не в индексе, добавьте, вы по ним делаете выборку (хотя странно, в explain по ним ref, странная оптимизация)..

 

[KillDead]

судя по схеме, genre_connect.genre_id, genre_connect.film_idу вас не в индексе,

почему не в индексе?
`genre_connect` ( `film_id` int(11) NOT NULL, `genre_id` int(11) NOT NULL, KEY `film_id` (`film_id`), KEY `genre_id` (`genre_id`) )
если на два поля индекс создавать - KEY `genre_id_2` (`genre_id`,`film_id`) ситуация особо не меняется

 

[KillDead]

Здравствуйте. Это снова я. В общем нашёл я что эта штука называется 'постраничная навигация'. Вот нашёл очень хорошее описание
Для просмотра ссылки Войди или Зарегистрируйся
Хотя как делать- я так и не понял. Автор сортирует по полю, но что вообще он делает...

если требуется сортировка по полю, значения которого могут повторяться? Например, height в нашем случае. Простым запросом было выяснено, что в таблице каждое значения height встречается примерно 800 раз. Просто передать последний выведенный height в параметрах запроса уже мало. Поможет нам всё тот же id. От нас просят отсортировать записи по высоте, но это ведь не мешает нам отсортировать их потом еще и по id?
Для этого нам понадобится новый индекс:

ALTER TABLE items ADD KEY height_id_idx (height, id);

Запрос для первой страницы будет такой:

SELECT SQL_NO_CACHE id, height FROM items ORDER BY height, id LIMIT 10;

В моих результатах у последней записи height=0, id=1174.
Так и надо передать следующей странице. Например, next_0_1174 или next/0/1074 – как Вам удобнее.
Теперь нам нужно выбрать записи, у которых либо height больше 0, либо height=0, а id>1174 (именно для этого мы и сделали дополнительную сортировку).
То есть:

SELECT SQL_NO_CACHE * FROM items WHERE (height>0) OR (height=0 AND id>1174) ORDER BY height, id LIMIT 10;

хм, он тупо создал таблицу где height и id идёт по порядку?

Ну хорошо, но как мне быть, есть стратегии?
Есть, хз, по моему совершенно безумное решение-
1. Создать таблицы genre_{id жанра }
id | film_id | rating
где поля будут отсортированы по rating. Будет куча таблиц...

 

[BDSG]

пардон, с индексами не внимательно посмотрел.. да и на деле у вас limit используется.. тут важно что понимать: когда вы делаете limit start offset, сначала делается полная(!) выборка (со всеми условиями where и сортировками), и только потом курсор ставится на start и отсекается offset полей.. автор на хабре (в принципе так делается на многих движках) предлагает сразу курсор ставить на нужное место (при использовании индекса это относительно быстро) и от этого места брать нужный offset.. само собой, поскольку не происходит полной вычитки таблицы, работает это много быстрее..

не очень, правда, понял как это относится с самому первому посту, в котором вам надо получить полный dataset по условию.. в нём, кстати, узкое место: left(!) join - открытое сязывание.. так будет оптимальнее:
SELECT *, (перечень полей из cat) FROM product
UNION
SELECT * FROM product JOIN cat ON cat.product_id = product.id WHERE cat.id= 666
если же ещё нужны лимиты, добавьте, но только с offset и правильной сортировкой..

 

[KillDead]

спасибо за ответы . Да, я чтото упустил из виду как работает лимит. Просмотрев хабр просветился.

в нём, кстати, узкое место: left(!) join - открытое сязывание.. так будет оптимальнее:
SELECT *, (перечень полей из cat) FROM product
UNION
SELECT * FROM product JOIN cat ON cat.product_id = product.id WHERE cat.id= 666
если же ещё нужны лимиты, добавьте, но только с offset и правильной сортировкой..

хмммм, ты предлагаешь просто заменить LEFT JOIN на JOIN? да, помогло. Спасибо, скорость подросла.

Но вот с ORDER BY всё равно адски тормозит.
SELECT * FROM product JOIN cat ON cat.product_id = product.id WHERE cat.id= 666 ORDER BY rating ASC
как делать правильную сортировку?

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

я предложил просто создавать таблицы к примеру для категории 666
cat_666 -> id | film_id | rating Методом Insert cat_666 Select (0, id, rating) From product ORDER BY rating ASC
теперь можно тупо выбрать ИД фильмов для страницы 10000 категории 666, отсортированной по рейтингу
SELECT film_id FROM cat_666 WHERE id > 10000*PER_PAGE-1 LIMIT PER_PAGE
в комментах там подобное предлагали.
Нагрузка минимальная вроде, хотя в итоге к примеру для 30 категорий и для сортировки по 5-ти параметрам нам нужно создавать 150 таких вот таблиц.....

 

[BDSG]

хмммм, ты предлагаешь просто заменить LEFT JOIN на JOIN?

нет, т.к. это разные логические конструкции.. я предложил заменить left join на union + join.. т.е. с сохранением логики выражения..

поле rating не в индексе, да?.. в статье, кстати, об этом писалось..

зы.. целиком бы задачу.. а то по ходу обсуждения внезапно появляются всё новые и новые нюансы..

 

[KillDead]

поле rating не в индексе, да?.. в статье, кстати, об этом писалось..

в индексе. Вввёл в индекс. Но чтото всё равно тормоз.

зы.. целиком бы задачу.. а то по ходу обсуждения внезапно появляются всё новые и новые нюансы..

сорри. На самом деле задача то, реально обычная, но для большого объёма. Как писал -2 таблицы . СвязьЖанраС Фильмами И Фильмы .
Нужно вывести фильмы (т.е как на обычном сайте, с навигацией по страницам 1,2,3,4,5 ) Для Каталога 666, Отсортированные по Рейтингу (либо по Цене). Всё.

Вот состав 2-х таблиц

`genre_connect` (
  `film_id` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  KEY `film_id` (`film_id`),
  KEY `genre_id` (`genre_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

`films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `price`int(5) NOT NULL,
  `rating` varchar(100) NOT NULL,
... ещё 15 полей

   `genre_ids` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rating` (`rating`),
  FULLTEXT KEY `short_story` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251

Если бы мне не нужно было сортировать по цене я бы сделал как на хабре- установил id в нужное место и отсчитал. Там есть о сортировке с параметром которую я не совсем понял . Вроде вот и всё