Войти в систему

Home
    - Создать дневник
    - Написать в дневник
       - Подробный режим

LJ.Rossia.org
    - Новости сайта
    - Общие настройки
    - Sitemap
    - Оплата
    - ljr-fif

Редактировать...
    - Настройки
    - Список друзей
    - Дневник
    - Картинки
    - Пароль
    - Вид дневника

Сообщества

Настроить S2

Помощь
    - Забыли пароль?
    - FAQ
    - Тех. поддержка



Пишет mumuntu ([info]mumuntu)
@ 2010-09-07 13:41:00
Previous Entry  Add to memories!  Tell a Friend!  Next Entry
Простенькая задачка
Дано: MySQL
В нем есть таблица блогозаписей, таблица тегов и таблица связей тег-запись, т.е., ситуация более-менее стандартная.
Требуется: написать запрос(ы), выбирающий 100 первых блогозаписей по тегам "кровь", "любовь", "морковь", "свекровь", отсортированных в порядке написания блогозаписи.
Прежде чем писать в комментарии ответы, посмотрите, пожалуйста, план вашего запроса.
UPD: планы и сами запросы кидайте в комментарии к записи, обсудим.

(Добавить комментарий)


[info]jamhed@lj
2010-09-07 05:45 (ссылка)
А таблицу тегов не проще держать в виде (tag text, record_id integer)?

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 05:54 (ссылка)
Будем считать, что это не теги, а категории, и мы над ними тоже должны производить какие-то операции.

(Ответить) (Уровень выше)


[info]openxe@lj
2010-09-07 05:54 (ссылка)
Думаю в этом случае, лучше добавить избыточное поле "Дата" в таблицу связи тег-запись.
Логика может чуть усложниться, но зато всё можно будет просто отсортировать.

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:15 (ссылка)
Да похоже что не выйдет, проблему с группировкой айдишек самих записей это не решит.
У нас drving table будет тег-запись, а нам же еще группировка нужна, причем, по ключу другой таблицы, заранее могу сказать, что MySQL этого не перенесет.

(Ответить) (Уровень выше)


[info]kvasimodo@lj
2010-09-07 06:07 (ссылка)
я не знаю, что такое MySQL, но все равно не понимаю, какие могут быть варианты?
select top 100 блогозаписи.*
from блогозаписи
join теги-записи on теги-записи.запись_айди = блогозаписи.запись_айди
join теги on теги-записи.тег_айди = теги.тег_айди
where тег.имя in ('кровь', 'любовь', 'морковь', 'свекровь')
order by блогозаписи.дата

или я чота не так понял

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:11 (ссылка)
Ну почти.
В таком запросе будут дубли, если запись помечена двумя и более тегами, а такое предполагается сплошь и рядом.
То есть, в резалтсете будут записи с одинаковыми PK.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 06:28 (ссылка)
В общем

group by блогозаписи.ID
order by блогозаписи.дата

В остальном все так и есть
Проблема в том, что при разных выражениях group by и order by отрубается индекс, а его неиспользование приводит на боевой базе к полному краху всего из шести букв

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]kvasimodo@lj
2010-09-07 06:39 (ссылка)
ну тогда вот так
create table posts (post_Id int identity(1,1), text nvarchar(max), date datetime)
create table tags (tag_Id int identity(1,1), name nvarchar(255))
create table tagsposts (tag_id int, post_id int)

insert posts (text, date)
select top 600 name, crdate from sysobjects

insert tags (name )
select 'кровь' union select 'любовь' union select 'морковь' union select 'свекровь' union select 'хуй'

insert tagsposts (tag_id, post_id)
select (select tag_id from tags where name ='кровь'), post_id
from posts where post_id between 1 and 200
union
select (select tag_id from tags where name ='любовь'), post_id
from posts where post_id between 100 and 300
union
select (select tag_id from tags where name ='морковь'), post_id
from posts where post_id between 200 and 400
union
select (select tag_id from tags where name ='свекровь'), post_id
from posts where post_id between 300 and 500
union
select (select tag_id from tags where name ='хуй'), post_id
from posts where post_id between 400 and 600

select top 100 * from posts
join (
select post_id
from tagsposts
join tags
on tags.tag_id = tagsposts.tag_id
where tags.name in ('кровь' ,'любовь' ,'морковь' ,'свекровь')
group by post_id
) tp
on tp.post_id = posts.post_id
order by posts.date

drop table posts
drop table tags
drop table tagsposts

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:41 (ссылка)
Отлично, отлично, сейчас буду глядеть план!
Я до сих пор не могу привыкнуть делать join прямо на select.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-07 06:51 (ссылка)
похоже это то что надо, гораздо лучше моего варианта.

(Ответить) (Уровень выше)

Re: Відповідь на ваш коментар...
[info]kvasimodo@lj
2010-09-07 07:01 (ссылка)
но вообще лучше конечно без красот - строить нужные индекса, сбрасывать во временную таблицу, при необходимости индексировать и ее, а потом писать маленький аккуратный запрос к главной

(Ответить) (Уровень выше) (Ветвь дискуссии)

Re: Відповідь на ваш коментар...
[info]m_a_m_o_n@lj
2010-09-07 07:27 (ссылка)
На счёт строить индексы.
В постгрисе можно построить full-text-search индекс
по тегам и хранить его в атрибуте таблицы blog.

Запрос будет типа:
select *
from blog
where tag_fts_index @@ to_tsquery('Тёща & не прощу')
order by date
limit 100;

Обновлять атрибут можно триггером.

P.S. в mysql тоже есть полнотекстовый поиск, так что
возможно это вариант.

(Ответить) (Уровень выше) (Ветвь дискуссии)

Re: Відповідь на ваш коментар...
[info]alexclear@lj
2010-09-07 07:55 (ссылка)
> P.S. в mysql тоже есть полнотекстовый поиск, так что
возможно это вариант.

Нет, он там только на MyISAM таблицах, не подойдет

(Ответить) (Уровень выше)

Re: Відповідь на ваш коментар...
[info]erraen@lj
2010-09-07 14:55 (ссылка)
А можно - атрибутом, содержащим массив интов (id тегов).
GIN/GIST индексы поверх int[] тоже есть.

(Ответить) (Уровень выше) (Ветвь дискуссии)

Re: Відповідь на ваш коментар...
[info]m_a_m_o_n@lj
2010-09-08 04:13 (ссылка)
Целевая база mysql, postgres я для примера привёл.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 13:06 (ссылка)
При INNER JOIN на SELECT отрубается индекс для сортировки.
Если его убрать, то не отрубается.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 13:27 (ссылка)
Вообще, похоже, что в мускуле наличие в запросе джойна на временную таблицу вырубает дальнейшее использование составного индекса просто.

(Ответить) (Уровень выше)


[info]gmother_arised@lj
2010-09-07 07:22 (ссылка)
а почему он отрубается?

попробовал у себя:
EXPLAIN SELECT *
FROM tag t
INNER JOIN post_tag pt ON pt.tid = t.tid
INNER JOIN post p ON p.pid = pt.pid
WHERE t.tag IN ('a', 'b')
GROUP BY p.pid
ORDER BY p.tm
LIMIT 50

результат:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | t | range | PRIMARY,tag | tag | [BINARY - 3Байт] | NULL | 2 | Using where; Using temporary; Using filesort
1 | SIMPLE | pt | index | PRIMARY | PRIMARY | [BINARY - 1Байт] | NULL | 158 | Using where; Using index
1 | SIMPLE | p | eq_ref | PRIMARY,pid | PRIMARY | [BINARY - 1Байт] | test.pt.pid | 1

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 07:57 (ссылка)
Ну, типа не может мыскль использовать индекс для группировки/сортировки, если там разные столбцы.

Да вот оно у Вас:

> Using temporary; Using filesort

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]gmother_arised@lj
2010-09-07 10:21 (ссылка)
Тут не в группировке/сортировке дело, filesort в данном случае будет при любых столбцах указанных в group by и order by (я попробовал). Майскуль в очень редких случаях умеет пользовать индекс для сортировки. Другое дело, что filesort сам по себе не так уж страшен, если у Вас не стотыщмиллионов постов находится по этим тегам.
А если их стотыщмиллионов, то нужно, наверное, каким-то умным алгоритмом ограничивать дату публикации в запросе. Чтобы, например, выбрать примерно 200+- постов, отсортировать и взять 100 последних. Если вдруг не хватило, то добрать ещё. Как-то так.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 13:16 (ссылка)
> Тут не в группировке/сортировке дело, filesort в данном случае будет при любых столбцах указанных в group by и order by (я попробовал).

Естественно, индекс придется форсить.
У меня этой кровью/любовью полтаблицы потежено, индекс отрубается просто из-за мощности выборки.
Так вот, форся индекс и делая вложенные подзапросы, мне от файлсорта удалось уйти, но я споткнулся на количестве вызова этого вложенного подзапроса.

> Майскуль в очень редких случаях умеет пользовать индекс для сортировки.

Ну как, все эти случаи описаны в документации. Вернее, описано, когда не умеет. Я вот хочу, чтобы умел.

> Другое дело, что filesort сам по себе не так уж страшен, если у Вас не стотыщмиллионов постов находится по этим тегам.

Их, к сожалению, 28 тыщ примерно, что составляет треть базы.
И файлсорт у меня длится две секунды.
Хорошо, я могу тянуть не все поля, а только ID, так уже пробовал, будет сильно быстрее. Но при большой нагрузке на сайт и это начинает проседать.

(Ответить) (Уровень выше)


[info]b00ter@lj
2010-09-07 06:09 (ссылка)
А денормализовать и сделать поиск по текстовому полю - неспортивно, не?

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:13 (ссылка)
Я не понимаю, почему все СРАЗУ начинают предлагать какие-то варианты, связанные с изменением структуры базы.
Мы ведь еще не выяснили, а что сейчас плохо.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]b00ter@lj
2010-09-07 06:16 (ссылка)
Ну, может потому что вопрос изначально поставлен как "поглядите какой грешный mysql". :)
Но я бы денормализацию сделал бы изначально - это в любом случае будет дешевле, чем дергать три таблицы для выдачи поста.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:24 (ссылка)
> Ну, может потому что вопрос изначально поставлен как "поглядите какой грешный mysql". :)

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

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

Ну начинается.
Я тут читал книгу "SQL Tuning" орейлевскую, так автор там и восемь джойнов не боится делать, почему я должен бояться?
Понятно, что дело идет к денормализации, но это приведет к появлению дополнительной логики прямо в базе, триггеры и т.д.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]b00ter@lj
2010-09-07 06:27 (ссылка)
> Сдается мне, тут не мускуль грешный, а разработчики блог-движка, в котором я на этот запрос втыкаю.

А что за движок?

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

Автор это для табличек с сотнями тысячами записей делал или просто показывал, какой SQL красивый и как там все изящно можно сделать?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:30 (ссылка)
> А что за движок?

WordPress

> Автор это для табличек с сотнями тысячами записей делал или просто показывал, какой SQL красивый и как там все изящно можно сделать?

Если не врет, то делал, верю ему на слово.
Так а почему это должно быть сильно медленнее?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]b00ter@lj
2010-09-07 06:32 (ссылка)
> Так а почему это должно быть сильно медленнее?

А насколько сильно? Может быть [info]zabivator@lj-а попытать?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:37 (ссылка)
Попытаю, куда денусь.
Но он больше по физплану специалист, чем по оптимизации запросов.
Я еще сам посижу-повтыкаю, к слову, мне удалось переписать их запрос так, чтобы он использовал только индексы, я его переписал как SELECT ... WHERE IN(SELECT ...), что приводит к выполнению вложенного селекта на каждую строчку внешнего, сейчас попробую сделать SELECT FROM SELECT, может, станет получше.

(Ответить) (Уровень выше)


[info]metaclass@lj
2010-09-07 06:53 (ссылка)
Да не настолько те джоины медленнее, особенно по первичным-уникальным ключам когда они.
В общем, когда начинается проблема что джоины на что-то сильно влияют - очень вероятно, что там и многое другое будет тормозить и одной денормализацией еще не факт что обойдешся.

(Ответить) (Уровень выше)


[info]b00ter@lj
2010-09-07 06:28 (ссылка)
А-а-а, WP.

(Ответить) (Уровень выше)

база - sakila
[info]m_a_m_o_n@lj
2010-09-07 06:14 (ссылка)

explain select *
from (select distinct film_id, release_year
from film
join film_actor using (film_id)
join actor using (actor_id)
where actor.first_name in ('PENELOPE', 'CHRISTIAN', 'LUCILLE', 'SANDRA')
order by release_year, film_id
limit 1000
) sub
order by release_year, film_id
limit 100;



1, 'PRIMARY', '', 'ALL', '', '', '', '', 246, 'Using filesort'
2, 'DERIVED', 'film', 'ALL', 'PRIMARY', '', '', '', 953, 'Using temporary; Using filesort'
2, 'DERIVED', 'film_actor', 'ref', 'PRIMARY,idx_fk_film_id', 'idx_fk_film_id', '2', 'sakila.film.film_id', 2, 'Using index; Distinct'
2, 'DERIVED', 'actor', 'eq_ref', 'PRIMARY', 'PRIMARY', '2', 'sakila.film_actor.actor_id', 1, 'Using where; Distinct'

(Ответить) (Ветвь дискуссии)

Re: база - sakila
[info]alexclear@lj
2010-09-07 06:20 (ссылка)
Using filesort
Using temporary; Using filesort

Это вилы
У меня на базе все именно так
Файлсорт проходит сейчас за две секунды, я знаю, как уменьшить это время, в принципе, но пытаюсь решить задачу не для 70000 записей, а, скажем, для 7000000
Понятно, что никто и никогда (я надеюсь) не будет гонять WP с 7000000 постов в базе, но хотелось бы иметь более толковое решение, чем просто уменьшение размера того, что кладется в буфер сортировки

(Ответить) (Уровень выше) (Ветвь дискуссии)

Re: база - sakila
[info]m_a_m_o_n@lj
2010-09-07 06:31 (ссылка)
Ещё можно вот так:

explain select *
from (select distinct *
from (select film_id, release_year
from film
join film_actor using (film_id)
join actor using (actor_id)
where actor.first_name in ('PENELOPE', 'CHRISTIAN', 'LUCILLE', 'SANDRA')
order by release_year, film_id
limit 400 -- 100*4
) sub1) sub
order by release_year, film_id
limit 100;



1, 'PRIMARY', '', 'ALL', '', '', '', '', 246, 'Using filesort'
2, 'DERIVED', '', 'ALL', '', '', '', '', 291, 'Using temporary'
3, 'DERIVED', 'actor', 'range', 'PRIMARY,idx_actor_first_name', 'idx_actor_first_name', '137', '', 11, 'Using where; Using index; Using temporary; Using filesort'
3, 'DERIVED', 'film_actor', 'ref', 'PRIMARY,idx_fk_film_id', 'PRIMARY', '2', 'sakila.actor.actor_id', 13, 'Using index'
3, 'DERIVED', 'film', 'eq_ref', 'PRIMARY', 'PRIMARY', '2', 'sakila.film_actor.film_id', 1, ''

(Ответить) (Уровень выше) (Ветвь дискуссии)

Re: база - sakila
[info]m_a_m_o_n@lj
2010-09-07 06:32 (ссылка)
(я добавил индекс по "имени тега" - actor.first_name,
обратить внимание на первую строчку в плане)

(Ответить) (Уровень выше)


[info]bearbeer@lj
2010-09-07 06:18 (ссылка)
**100 первых
- это с какой стороны? 100 самых старых по дате? 100 первых созданных (дата появления в блоге - отдельное поле-то)?

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:22 (ссылка)
По дате создания, с любой стороны, пусть будут самые свежие

(Ответить) (Уровень выше)


[info]metaclass@lj
2010-09-07 06:36 (ссылка)
select first 100 * from posts
where exists
(select 1 from tags
join posttags on posttags.tag_id=tags.tag_id and posttags.post_id=posts.post_id
where tags.tag_value in ('черви','жабы','змеи')
)
order by blogpost_dt

MySQL нету, подходящей по размеру базы тоже, план проверить не на чем.

Если план будет очень хреновый, с бы таки сначала выбрал по значениям тэгов список их id, а затем уже его использовал в запросе, не трогая таблицу tags

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 06:39 (ссылка)
О, отлично, сейчас буду проверять, очень может быть, что это ОНО.

> Если план будет очень хреновый, с бы таки сначала выбрал по значениям тэгов список их id, а затем уже его использовал в запросе, не трогая таблицу tags

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

(Ответить) (Уровень выше)


[info]m_a_m_o_n@lj
2010-09-07 06:53 (ссылка)
Сомневаюсь что это будет работать для mysql, он не любит подзапросы, это не постгрис.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]metaclass@lj
2010-09-07 06:54 (ссылка)
Ужасно, неужели все настолько печально?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-07 07:03 (ссылка)
Ну да, где то в 4-ро медленне чем в варианте
с подзапросом в join-не.

В плане он всю таблицу blog (у меня film) шарашит, как я думал.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 13:42 (ссылка)
Это нормально, потому что тегами все протежено вдоль и поперек, одним тегом может быть помечено 30-50% таблицы, тут сканить по индексу просто смысла нет вообще.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 13:39 (ссылка)
Походу, это единственное, что сработало.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-08 04:16 (ссылка)
Очень интересно узнать, скольно в тестовой базе
постов, тегов и связей. И время по запросам здесь приведённым.

В sakila всего 1000 записей в films, все запросы
слишком быстро отрабатывают что бы разница была видна достоверно.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 09:47 (ссылка)

mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
| 72645 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from wp_term_relationships;
+----------+
| count(*) |
+----------+
| 186297 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from wp_term_taxonomy;
+----------+
| count(*) |
+----------+
| 17442 |
+----------+
1 row in set (0.00 sec)

Зачем 17442 тегов/категорий, я не знаю, редакторы что-то намудили, в реальности при выборках используется штук может 50, одновременно в запросе - штук 10.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-08 09:49 (ссылка)
> И время по запросам здесь приведённым.

Для исходного, тривиального, около двух секунд.
Для варианта коллеги [info]metaclass@lj - 0.05 секунды для LIMIT 3000,100
Вариант коллеги [info]kvasimodo@lj пришлось прервать, не дождался конца, как и мой вариант с SELECT ... WHERE ID IN(SELECT ...)
Остальные варианты не смотрел.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-08 10:26 (ссылка)
>Для варианта коллеги [info]metaclass - 0.05 секунды для LIMIT 3000,100
mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
| 72645 |
+----------+
1 row in set (0.06 sec)

Что то я не понимаю, как запрос с full-scan для таблицы posts может
отработать быстрее чем count(*)?

Покажите пожалуйста план.

P.S. У mysql есть фишка, он хорошо кеширует результаты запросов,
то что первый раз он мог считать за 10 секунд, если запрос выполнить
повторно не меняя базу - результат выпадет в миллисекунды.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 10:29 (ссылка)
> Что то я не понимаю, как запрос с full-scan для таблицы posts может
отработать быстрее чем count(*)?

Почему с full scan? Там выборка по индексу.
Вот он план.
С увеличением номера страницы будет расти и время.
Для LIMIT 28300, 100 будет около 0.5 секунды.

> P.S. У mysql есть фишка, он хорошо кеширует результаты запросов,
то что первый раз он мог считать за 10 секунд, если запрос выполнить
повторно не меняя базу - результат выпадет в миллисекунды.

Ненене, меня этим не провести, я жду минуты по три между запросами, само собой.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-08 10:45 (ссылка)
Хмм, наверное mysql достаточно умный что бы проверять записи в том
порядке в котором он их выдаёт и остановиться когда хватит.

Интересно, а сколько времени займёт поиск по редкому тегу, для которого
меньше 100 записей в базе?

P.S. >Ненене, меня этим не провести, я жду минуты по три между запросами, само собой.
Можно просто запрос немного исправить, "--" где нибудь добавить или пробелов,
тогда он будет выполнен как новый.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 13:02 (ссылка)
> Хмм, наверное mysql достаточно умный что бы проверять записи в том
порядке в котором он их выдаёт и остановиться когда хватит.

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

> Интересно, а сколько времени займёт поиск по редкому тегу, для которого
меньше 100 записей в базе?

Полный просмотр всего множества, ограниченного первыми двумя условиями.
Там где-то половина всей таблицы и выходит.
То есть, лучше, в данном случае, когда теги часто встречаются - быстрее сотня наберется.

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 13:36 (ссылка)
ВАУ!
Да, это оно!
Спасибо, очень круто!

(Ответить) (Уровень выше)


[info]aefimov@lj
2010-09-07 16:58 (ссылка)
Это не mysql :)

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 01:38 (ссылка)
Для мыскля немного другой запрос, но этот подход отлично сработал.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]aefimov@lj
2010-09-08 06:42 (ссылка)
Покажи, чем заменяется exists в mysql? И почему мой вариант с джойнами полная фигня, тоже можешь осветить, в двух словах?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 09:37 (ссылка)
EXISTS ничем не заменяется, он в мыскле и так есть, заменяется first 100, стандарно, limit 0,100
Вариант с джойнами плох тем, что в нем план, выбираемый мысклем, берет за первую таблицу не posts, а tags, что хорошо работало бы, если бы не группировка и сортировка. Из-за этого мыскль делает группировку и сортировку через временные буферы, а не используя индекс над posts.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]aefimov@lj
2010-09-08 09:51 (ссылка)
Ясно, спасибо. Про EXISTS не знал :)

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-08 09:51 (ссылка)

(Ответить) (Уровень выше)

(Комментарий удалён)

[info]ext_55374@lj
2010-09-08 22:47 (ссылка)
Ботъ фофык.

(Ответить) (Уровень выше)


[info]bopm@lj
2010-09-07 06:46 (ссылка)
После дано можно не читать.

(Ответить)


[info]blacklion@lj
2010-09-07 08:23 (ссылка)
Какой тебе план без живых данных — без статистики. Или MySQL'ю пох?

(Ответить) (Ветвь дискуссии)


[info]blacklion@lj
2010-09-07 08:30 (ссылка)
SELECT FIRST/TOP N для меня новость. А мне казалось, я SQL неплохо знаю...

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 12:26 (ссылка)
По-моему, это диалект MSSQL

(Ответить) (Уровень выше)


[info]alexclear@lj
2010-09-07 12:35 (ссылка)
А кому не пох? Здесь вариантов плана не так много.
Фактически, я его могу руками указать.

(Ответить) (Уровень выше)


[info]lazyreader@lj
2010-09-07 14:39 (ссылка)
А что, намечается постгресомысклосрач?

В постгрескле я бы просто сделал

select * from records where id in (select distinct record_id from record_tags where tag_id in (select id from tags where tag in ('foo', 'bar', 'baz', 'qux'))) order by ts limit 100;

снабдив, разумеется, надлежащими индексами (но не больше).

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

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-07 14:56 (ссылка)
> А что, намечается постгресомысклосрач?

Срач будет очень коротким.
Я мыскль по доброй воле не использую, я ж не Александр Горный, или там мейлру какое-нибудь.

> select * from records where id in (select distinct record_id from record_tags where tag_id in (select id from tags where tag in ('foo', 'bar', 'baz', 'qux'))) order by ts limit 100;

Собственно, весь вопрос в том, умеет ли СУБД делать сортировку и группировку уже после того, как все извлекли, или надо вокруг нее приседать.
Вот в Вашем запросе мыскль сломался бы на том, что distinct это та же группировка, после чего я поимел бы ту же проблему, которая в вордпресс и так уже есть.

(Ответить) (Уровень выше)


[info]gornal@lj
2010-09-07 16:35 (ссылка)
Я бы сделал 4 запроса и объединение в php.

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 01:29 (ссылка)
Это будет так называемый MapReduce со всеми вытекающими последствиями.
По-моему, излишняя сложность, да и блогпостов по тегам может быть много - редакторы сайта тегируют что попало чем попало, до 50% новостей могут идти под тегом "новости" или "происшествия". Объединять 4 резалтсета один из которых содержит тысяч под 20 записей - PHP справится, конечно, но будет ли быстрее, чем на СУБД?

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]m_a_m_o_n@lj
2010-09-08 05:07 (ссылка)
И вот тут реальность показала своё истинное лицо.
1. Выборка постов по тегам это ключевая функциональность,
иначе бы ей не удалялось столько внимания.
2. Статистика распределения тегов предпологается крайне
специфичной, мало тегов, много записей помечено одним
и тем же тегом.
3. К выборке есть хитрое ограничение - количество записей,
которое противоречит тому что запись может быть помечена
несколькими тегами, а значит где то в недрах запроса будет
группировка (скрытая или явная).
4. Мораль. ПОДХОД НЕ ПРАВИЛЬНЫЙ.

Раз такое дело, я считаю нужно оптимизировать структуру данных,
так что бы получть качественное преимущество.
Есть два варианта, добавить поле дата в связь поста и тега
но это вроде как не круто.

ВТОРОЙ ВАРИАНТ: всё таки использовать полнотекстовый поиcк:

-- добавить в табличку атрибут для хранения всех тегов разом (для его обновления можно использовать триггер, если теги нужны и отдельно).
alter table film add column tag_index varchar(255);
-- заполняем атрибут данными (то что делал бы триггер).
update film set tag_index = (select lower(group_concat(first_name SEPARATOR ',')) from actor join film_actor fa using(actor_id) where fa.film_id=film.film_id);
-- тестируем, оказыватся есть фильмы без актёров.
select * from film where tag_index is null;
-- а вот тут пошёл косяк, полнотекстовый индекс нельзя создавать на Innodb,
-- так что я копирую таблицу что бы дальнейшие игры не поломали мне тестовую базу.
create table film_2 like film;
-- а вот на myisam fulltext индекс работать будет (С 2004 ГОДА ЭТА ПРОБЛЕМА)
ALTER TABLE film_2 ENGINE = MYISAM;
delete from film_2; -- я несколько раз заполнял
insert into film_2 select * from film; -- заполняем таблицу.
alter table film_2 add fulltext(tag_index); -- создаём индекс.

-- а вот собствнно и запрос, выполняется чертовски быстро.
select *
from film_2
where MATCH(tag_index) AGAINST ('grace')
and film_id>100
order by film_id
limit 10;

-- думаю здесь будет качественное отличие перед любым вариантом
-- без изменения структуры базы.
-- да, тип таблицы должен быть myisam, но раз уж взяли mysql
-- играйте по его правилам.

(Ответить) (Уровень выше)


[info]gornal@lj
2010-09-08 08:45 (ссылка)
limit 100 же в каждом из 4-ех запросов можно делать.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 09:23 (ссылка)
Да, для самой первой страницы это валидно, но потом пользователь захочет листать дальше.
Правда, я забыл об этом упомянуть в условии.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]gornal@lj
2010-09-08 10:06 (ссылка)
1000-ую страницу в таких условиях с разумными усилиями и за разумное время mysql сделать не сможет. (Сомневаюсь, что даже хороший план из комментариев выше поможет, проверять не на чем).

Но, мне кажется, в жизни вряд ли кому-то понадобится более чем 10 страниц по 100 элементов, а склеить 4 массива по 1000 элементов php сможет легко.

(Ответить) (Уровень выше) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 10:17 (ссылка)
1000-ю - да, надеюсь, что я никогда не увижу блог с 1000 страниц по 100 элементов.
283-ю выбирает за 0.66 секунды, что неплохо для такой глубины, туда только поисковики и ходят.
Для human users, конечно, достаточно PHP.

(Ответить) (Уровень выше)


[info]aefimov@lj
2010-09-07 17:07 (ссылка)
Я не большой знаток mysql, но что тут не так:
select p.* from posts p
join post_tags pt on pt.post_id = p.id
join tags t on pt.tag_id = t.id
where t.tag in ('кровь', 'морковь', 'любовь', 'свекровь')
order by p.post_time desc limit 100;
?

Ну если там join по всем тегам и постам, то, можно внести t.tag in ... в join попробовать

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 01:33 (ссылка)
Конкретно в этом запросе надо добавить group by p.id, иначе в резалтсете будут дубликаты айдишек для тех записей, которые помечены двумя и более тегами.
И вот после добавления group by с планом запроса начинается самое интересное.

(Ответить) (Уровень выше)


[info]bmikle@lj
2010-09-07 21:01 (ссылка)
А что, в MySQL слова distinct нет совсем?

(Ответить) (Ветвь дискуссии)


[info]alexclear@lj
2010-09-08 01:31 (ссылка)
А толку от него?
План запроса со словом distinct выходит такой, что 2 секунды на запрос.

(Ответить) (Уровень выше)