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

vasya · 18.08.2013 00:34:14

Debian написал:
-- Структура таблицы `tags`
--

CREATE TABLE IF NOT EXISTS `tags` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`image_id` int(10) unsigned NOT NULL,
`category_id` smallint(5) unsigned NOT NULL,
`tag` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `image_id` (`image_id`),
KEY `category_id` (`category_id`),
KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Задача:

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

Debian · 18.08.2013 00:37:02

Аааа, вы сказали что лишнее, я и удалил, а что нужно добавить, не понял, сейчас добавлю, гляну что получится.

Debian · 18.08.2013 00:55:00

Я конечно не спец, как вы, но мне кажется, что мой вариант, работает лучше вашего, судя по результатам затронутых рядов, вот результат explain

mysql> EXPLAIN SELECT DISTINCT i.id, i.title, i.upload_date FROM images i

    -> JOIN (SELECT image_id, tag FROM tags WHERE image_id != 19 AND tag IN('желтые', 'цветы', 'красивая', 'зелень')) t

    -> ON i.id = t.image_id AND i.category_id = 6

    -> ORDER BY FIELD (t.tag, 'желтые', 'цветы', 'красивая', 'зелень'), i.id DESC LIMIT 3\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: i

         type: ref

possible_keys: PRIMARY,category_id

          key: category_id

      key_len: 2

          ref: const

         rows: 5

        Extra: Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ref

possible_keys: <auto_key0>

          key: <auto_key0>

      key_len: 4

          ref: picksar.i.id

         rows: 2

        Extra: Distinct

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: tags

         type: range

possible_keys: tag,image_id

          key: tag

      key_len: 96

          ref: NULL

         rows: 11

        Extra: Using where; Using index

3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id = t.image_id\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: NULL

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: i

         type: eq_ref

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 4

          ref: t.image_id

         rows: 1

        Extra: NULL

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: tags

         type: range

possible_keys: tag,image_id

          key: image_id

      key_len: 4

          ref: NULL

         rows: 81

        Extra: Using where; Using temporary; Using filesort

3 rows in set (0.00 sec)

Debian · 18.08.2013 01:18:20

Я извиняюсь, добавил индекс на category_id, но все равно результат хуже, как на мой взгляд. Может составной индекс сделать?

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: NULL

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: i

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 17

        Extra: Using where; Using join buffer (Block Nested Loop)

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: tags

         type: ref

possible_keys: tag,image_id,category_id

          key: category_id

      key_len: 2

          ref: const

         rows: 24

        Extra: Using where; Using temporary; Using filesort

3 rows in set (0.00 sec)

vasya · 18.08.2013 01:18:25

Попробуйте указать использовать индекс tag в подзапросе
http://sqlinfo.ru/forum/viewtopic.php?id=231

Debian · 18.08.2013 01:26:43

А куда этот FORCE INDEX tag вставлять?

vasya · 18.08.2013 01:31:46

в подзапросе
.. FROM tags FORCE ..

Debian · 18.08.2013 01:38:41

Что-то не работает

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags FORCE INDEX tag WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id\G

ERROR 1064 (42000): 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 'tag WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цве' at line 1

vasya · 18.08.2013 01:41:10

.. FROM tags FORCE INDEX (tag) ..

Debian · 18.08.2013 01:44:36

Все равно хуже

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags FORCE INDEX (tag) WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: NULL

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: i

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 17

        Extra: Using where; Using join buffer (Block Nested Loop)

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: tags

         type: range

possible_keys: tag,image_id

          key: tag

      key_len: 96

          ref: NULL

         rows: 11

        Extra: Using index condition; Using where; Using temporary; Using filesort

3 rows in set (0.01 sec)

mysql>

На всякий случай покажу как сейчас выглядят таблицы:

--
-- Структура таблицы `tags`
--

CREATE TABLE IF NOT EXISTS `tags` (

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

  `image_id` int(10) unsigned NOT NULL,

  `category_id` smallint(5) unsigned NOT NULL,

  `tag` varchar(30) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `tag` (`tag`,`image_id`),

  KEY `image_id` (`image_id`),

  KEY `category_id` (`category_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=107 ;

--
-- Структура таблицы `images`
--

CREATE TABLE IF NOT EXISTS `images` (

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

  `user_id` int(10) unsigned NOT NULL,

  `category_id` smallint(5) unsigned NOT NULL,

  `title` varchar(100) NOT NULL,

  `filesize` int(8) unsigned NOT NULL,

  `width` smallint(4) unsigned NOT NULL,

  `height` smallint(4) unsigned NOT NULL,

  `color` char(6) NOT NULL,

  `color_percent` decimal(4,2) unsigned NOT NULL,

  `upload_date` int(10) unsigned NOT NULL,

  `views` int(10) unsigned NOT NULL DEFAULT '0',

  `downloads` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `user_id` (`user_id`),

  KEY `category_id` (`category_id`),

  KEY `color` (`color`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

vasya · 18.08.2013 01:59:06

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags FORCE INDEX (tag) WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id\G

запрос не полностью привели

Debian · 18.08.2013 02:05:50

Прошу прощения, где-то потерял кусочек, вот теперь что:

mysql> EXPLAIN SELECT i.id, i.title, i.upload_date FROM (SELECT DISTINCT image_id FROM tags FORCE INDEX (tag) WHERE image_id != 19 AND category_id = 6 AND tag IN('желтые', 'цветы', 'красивая', 'зелень') ORDER BY FIELD (tag, 'желтые', 'цветы', 'красивая', 'зелень'), image_id DESC LIMIT 3) t STRAIGHT_JOIN images i ON i.id = t.image_id\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: NULL

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: i

         type: eq_ref

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 4

          ref: t.image_id

         rows: 1

        Extra: NULL

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: tags

         type: range

possible_keys: tag,image_id

          key: tag

      key_len: 96

          ref: NULL

         rows: 11

        Extra: Using index condition; Using where; Using temporary; Using filesort

3 rows in set (0.00 sec)

Debian · 18.08.2013 02:09:38

Получается, что ваш вариант всеже лучше, один минус в том что пришлось добавить еще одно поле.

Спасибочки, буду обдумывать ваш подход.

vasya · 18.08.2013 02:14:32

Красота. Подзапрос дает всегда 3 уже отсортированные строки для каждой из которых есть 1 соответствие в таблице картинок (доступ по уникальному ключу).
В вашем случае выбираются все картинки данной категории (и с ростом базы их будет не 5), каждой из них соответствует несколько значений в результатах подзапроса, а потом ещё сортировать нужно прежде чем оставить 3 записи.

P.S. Главное, что нужно помнить: преждевременная оптимизация - зло.

Debian · 18.08.2013 02:17:39

Еще раз большое вам спасибо

Debian · 19.08.2013 17:28:06

Здравствуйте! Проверьте пожалуйста запросики Добавил в таблицу images и tags новое поле status, это сделано для того, чтобы у изображений были статусы, типа загружено успешно, ожидает модерации, и так далее. Естественно нужно и многие запросы переписывать, но на данный момент интересуют 2 запросика, которые мы с вами делали, хотя я бы сказал даже не мы а вы сделали Я внес изменения, но не уверен, что я это сделал правильно, хочу чтобы вы, проверили и одобрили.

Вот так теперь выглядят таблички:

--

-- Структура таблицы `images`

--

CREATE TABLE IF NOT EXISTS `images` (

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

  `user_id` int(10) unsigned NOT NULL,

  `category_id` smallint(5) unsigned NOT NULL,

  `title` varchar(100) NOT NULL,

  `filesize` int(8) unsigned NOT NULL,

  `width` smallint(4) unsigned NOT NULL,

  `height` smallint(4) unsigned NOT NULL,

  `color` char(6) NOT NULL DEFAULT '000000',

  `color_percent` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',

  `upload_date` int(10) unsigned NOT NULL,

  `views` int(10) unsigned NOT NULL DEFAULT '0',

  `downloads` int(10) unsigned NOT NULL DEFAULT '0',

  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `category_id` (`category_id`),

  KEY `color` (`color`),

  KEY `user_id` (`user_id`,`status`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;

--

-- Структура таблицы `tags`

--

CREATE TABLE IF NOT EXISTS `tags` (

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

  `image_id` int(10) unsigned NOT NULL,

  `category_id` smallint(5) unsigned NOT NULL,

  `tag` varchar(30) NOT NULL,

  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`),

  KEY `tag` (`tag`,`image_id`),

  KEY `image_id` (`image_id`),

  KEY `category_id` (`category_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=125 ;

А вот запросы:

$query = "SELECT `t`.`tag`, COUNT(*) AS `count` FROM `tags` `f` JOIN (SELECT `id`, `tag` FROM `tags` WHERE `image_id` = '$id') `t`

                      ON `f`.`tag` = `t`.`tag` AND `f`.`status` = '2' GROUP BY `t`.`tag` ORDER BY `t`.`id`";

$query = "SELECT `i`.`id`, `i`.`title`, `i`.`upload_date` FROM (SELECT DISTINCT `image_id` FROM `tags` FORCE INDEX (`tag`)

                      WHERE `image_id` != '$id' AND status = 2 AND `category_id` = '$category_id' AND `tag` IN(".$tags.")

                      ORDER BY FIELD (`tag`, ".$tags."),

                      `image_id` DESC LIMIT 3) `t` STRAIGHT_JOIN `images` `i` ON `i`.`id` = `t`.`image_id`";

В первом запросе нужно чтобы отображались похожие картинки, только со статусом 2, во втором что бы отображались и подсчитывались только те у которых статус равен 2. Правильно ли я сделал? Они работают, но возможно при каких-то условиях и не сработают, или потеряют ту оптимизацию которой мы добивались.

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

#76 18.08.2013 00:34:14

Re: Подсчитать количество одинаковых тегов.

Debian написал:

#77 18.08.2013 00:37:02

Re: Подсчитать количество одинаковых тегов.

#78 18.08.2013 00:55:00

Re: Подсчитать количество одинаковых тегов.

#79 18.08.2013 01:18:20

Re: Подсчитать количество одинаковых тегов.

#80 18.08.2013 01:18:25

Re: Подсчитать количество одинаковых тегов.

#81 18.08.2013 01:26:43

Re: Подсчитать количество одинаковых тегов.

#82 18.08.2013 01:31:46

Re: Подсчитать количество одинаковых тегов.

#83 18.08.2013 01:38:41

Re: Подсчитать количество одинаковых тегов.

#84 18.08.2013 01:41:10

Re: Подсчитать количество одинаковых тегов.

#85 18.08.2013 01:44:36

Re: Подсчитать количество одинаковых тегов.

#86 18.08.2013 01:59:06

Re: Подсчитать количество одинаковых тегов.

#87 18.08.2013 02:05:50

Re: Подсчитать количество одинаковых тегов.

#88 18.08.2013 02:09:38

Re: Подсчитать количество одинаковых тегов.

#89 18.08.2013 02:14:32

Re: Подсчитать количество одинаковых тегов.

#90 18.08.2013 02:17:39

Re: Подсчитать количество одинаковых тегов.

#91 19.08.2013 17:28:06

Re: Подсчитать количество одинаковых тегов.

Board footer