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

Тема в разделе "Базы данных", создана пользователем KillDead, 17 сен 2013.

Модераторы: latteo
  1. KillDead

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    Здравствуйте. вроде бы простейшая задача- есть 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 найти в таблице продуктов, но тогда сортировка по рейтингу невозможна.

    Как быть?
     
    Vishez и latteo нравится это.
  2. BDSG

    BDSG

    Регистр.:
    28 фев 2009
    Сообщения:
    203
    Симпатии:
    109
    на cat.id, cat.product_id, product.id индексы стоят?.. какая субд?.. именно выбирается долго, или приложением выборка долго обрабатывается?..

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

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    да, извиняюсь, слишком мало вводных дал
    база - 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 секунд. Но при нагрузочном тестировании эта хрень напрочь тормозит работу.
     
    Последнее редактирование: 17 сен 2013
  4. BDSG

    BDSG

    Регистр.:
    28 фев 2009
    Сообщения:
    203
    Симпатии:
    109
    судя по схеме, genre_connect.genre_id, genre_connect.film_idу вас не в индексе, добавьте, вы по ним делаете выборку (хотя странно, в explain по ним ref, странная оптимизация)..
     
  5. KillDead

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    почему не в индексе?
    `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`) ситуация особо не меняется
     
  6. KillDead

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    Здравствуйте. Это снова я. В общем нашёл я что эта штука называется 'постраничная навигация'. Вот нашёл очень хорошее описание
    http://habrahabr.ru/post/44608/
    Хотя как делать- я так и не понял. Автор сортирует по полю, но что вообще он делает...
    хм, он тупо создал таблицу где height и id идёт по порядку?

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

    BDSG

    Регистр.:
    28 фев 2009
    Сообщения:
    203
    Симпатии:
    109
    пардон, с индексами не внимательно посмотрел.. да и на деле у вас 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 и правильной сортировкой..
     
    Последнее редактирование: 22 сен 2013
    KillDead нравится это.
  8. KillDead

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    спасибо за ответы . Да, я чтото упустил из виду как работает лимит. Просмотрев хабр просветился.
    хмммм, ты предлагаешь просто заменить 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 таких вот таблиц.....
     
  9. BDSG

    BDSG

    Регистр.:
    28 фев 2009
    Сообщения:
    203
    Симпатии:
    109
    нет, т.к. это разные логические конструкции.. я предложил заменить left join на union + join.. т.е. с сохранением логики выражения..

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

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

    KillDead

    Регистр.:
    11 авг 2006
    Сообщения:
    890
    Симпатии:
    558
    в индексе. Вввёл в индекс. Но чтото всё равно тормоз.


    сорри. На самом деле задача то, реально обычная, но для большого объёма. Как писал -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 в нужное место и отсчитал. Там есть о сортировке с параметром которую я не совсем понял . Вроде вот и всё