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

Лучше неверно будет тут продолжить, так будет понятнее

Здравствуйте! Нужна ваша профессиональная помощь. Нужно составить запрос, или запросы, для поиска картинок по ключевым словам.

Структура табличек:

Задача:

Нужно выбрать, SELECT `images`.* FROM `images` в которых есть например слово 'красивая', ключевые слова хранятся в табличке `tags`, поле `image_id`, указывает на то к какой картинке принадлежит это слово. Выше мы уже разобрались как слова записываются в табличку `tags` как там все это выглядит и храниться, думаю должно быть понятно. При всем этом, во время выборки нужно учитывать категорию, тоесть, нужно чтобы сначала в выборке шли картинки которые, относятся к конкретной категории, например id категории 5, после чего идут картинки из всех остальных категорий. Также отсортировать их по id таблицы `images`, чтобы сначала шли новые а потом старые, тоесть, с более больших id к более маленьким.

Не знаю, мне кажется именно так должен работать поиск, если я не прав то подскажите как обычно это делают, просто решаю такую задачу впервые.

Вот такая сложная задачка , не знаю как для Вас, но для меня не выполнимая, я конечно могу решить ее за счет средств PHP, циклов и т.д, но это будет ужас и кошмар, нагрузка будут ужасная. Можете составить запрос, или тут только в несколько запросов? Надеюсь на любую вашу помощь и советы, и пожалуйста проверьте также, правильно ли я сделал индексы у таблиц? Если еще нужна будет какая-то информация, скажите я добавлю любую необходимую информацию.

Чего-то не получилось картинку вставить, не пойму как она тут у вас вставляется, нажал обзор, выбрал, а она не вставилась

О разобрался

Отредактированно Debian (15.08.2013 16:43:04)

1. Вместо * лучше явно указывать нужные поля.

Ага, я знаю, это просто для примера.

2. Необходимость тех или иных индексов зависит от запросов. Посмотрите FAQ №5

Тоже знаю, но там как раз все индексы почти для этой работы с ключевыми словами, кроме:

3. Зачем в таблице тегов дублируется category_id?

Думал так будет легче, Вам придумать запрос Лишнее, удалить?

Ошибочку выдает ваш запрос.

Вау Безупречно Возле меня сидит товарищ, у него, да что там у него, у нас обоих челюсти поотваливались

Супер, вы бог MySQL Огромное приогромное Вам спасибо В точности по каждому пункту из моего задания, все условия выполняются Я уже люблю ваш форум Тут одни настоящие профи.

А вот тут:

Я так понял, мне средствами PHP, нужно указать номера категорий, в порядке, в котором нужно искать?

Хотя я вставил Ваш запрос без изменений, а картинки у меня сейчас только в 5 и 6 категории, а он все равно вывел из 6 так как положено. Объясните пожалуйста, что мне тут перечислять (category_id=5,1,2) ?

Отредактированно Debian (15.08.2013 17:54:25)

if(category_id=5,1,2)
если category_id = 5, то результатом функции будет 1, в противном случае 2

Таким образом в результате сортировки пойдут сначала картинки из 5ой категории, потом все остальные.
Средствами php вам нужно указывать только номер той категории, которая должна быть впереди. В данном примере это 5.

Debian написал:

Я просто в первые вижу такое <derived2>.

Это означает, что речь идет о таблице, полученной в результате выполнения подзапроса (см последнюю строку в explain, где тип доступа DERIVED и id=2).

Что касается улучшения, то разбивать на отдельные запросы. См http://webew.ru/articles/4856.webew

Сейчас немного поэксперементировал с индексами под этот запрос:

mysql> explain SELECT i.* FROM images i JOIN
    -> (SELECT DISTINCT image_id FROM tags WHERE tag='красивая') t ON i.id=t.image_id
    -> ORDER BY if(category_id=5,1,2), i.id DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |   86 | Using temporary; Using filesort |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY       | PRIMARY | 4       | t.image_id |    1 | NULL                            |
|  2 | DERIVED     | tags       | ALL    | NULL          | NULL    | NULL    | NULL       |   86 | Using where; Using temporary    |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql> explain SELECT i.* FROM images i JOIN
    -> (SELECT DISTINCT image_id FROM tags WHERE tag='красивая') t ON i.id=t.image_id
    -> ORDER BY if(category_id=5,1,2), i.id DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-----------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                                               |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |    6 | Using temporary; Using filesort                     |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY       | PRIMARY | 4       | t.image_id |    1 | NULL                                                |
|  2 | DERIVED     | tags       | ref    | tag           | tag     | 152     | const      |    6 | Using index condition; Using where; Using temporary |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

mysql> explain SELECT i.* FROM images i JOIN
    -> (SELECT DISTINCT image_id FROM tags WHERE tag='красивая') t ON i.id=t.image_id
    -> ORDER BY if(category_id=5,1,2), i.id DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |    6 | Using temporary; Using filesort |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY       | PRIMARY | 4       | t.image_id |    1 | NULL                            |
|  2 | DERIVED     | tags       | ref    | tag           | tag     | 152     | const      |    6 | Using where; Using index        |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql> explain SELECT i.* FROM images i JOIN
    -> (SELECT DISTINCT image_id FROM tags WHERE tag='красивая') t ON i.id=t.image_id
    -> ORDER BY if(category_id=5,1,2), i.id DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                                 |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |   44 | Using temporary; Using filesort       |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY       | PRIMARY | 4       | t.image_id |    1 | NULL                                  |
|  2 | DERIVED     | tags       | range  | tag           | tag     | 156     | NULL       |   44 | Using where; Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------------+
3 rows in set (0.01 sec)

mysql> explain SELECT i.* FROM images i JOIN
    -> (SELECT DISTINCT image_id FROM tags WHERE tag='красивая') t ON i.id=t.image_id
    -> ORDER BY if(category_id=5,1,2), i.id DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |    6 | Using temporary; Using filesort |
|  1 | PRIMARY     | i          | eq_ref | PRIMARY       | PRIMARY | 4       | t.image_id |    1 | NULL                            |
|  2 | DERIVED     | tags       | ref    | tag           | tag     | 92      | const      |    6 | Using where                     |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql>

Какой из этих вариантов самый оптимальный? Я так понимаю, что наверно последний? И не поделитесь информацией, где можно почитать про explain, на русском и доступным понятным языком?

Спасибо

"SELECT ... FROM images i JOIN (SELECT DISTINCT image_id FROM tags WHERE tag='$sort') t JOIN `categories` JOIN `users`
ON i.id=t.image_id AND `images`.`category_id` = `categories`.`id` AND `images`.`user_id` = `users`.`id`
ORDER BY if(category_id=5,1,2), i.id DESC LIMIT $start, $limit"

Наверно вы вот так хотели сделать? Просто вы назначили псевдоним images i а потом снова начали использовать images а не i.

Я не уверен проверьте пожалуйста, он заработал, но мало ли что.

Да, все так, только * в рабочем запросе писать не стоит.

Да вот я как раз и думаю над этой звездочкой, дело в том что эти запросы используются и на сайте и в админке, в админке почти все поля нужны, на сайте нет, и если перечислять поля, то придется еще делать копию всех этих запросов, для админки и для сайта, + там полей много получается огромнейший кусок кода, в 8 запросов, которые по сути всего отличаются в пару полей, вот я и думаю, а стоит ли оно того? Действительно ли на столько разница?

Отредактированно Debian (15.08.2013 22:21:12)