"сравнение" - объясните пожалуйста

[Spear]

Фанат
да, совсем забыл. я же ещё хотел узнать не быстрее ли будет использование mediumint(8) чем поле формата date?

пс
продолжая тему своего тяжелого для БД запроса.
появилась ещё такая проблемка:
если добавить к последнему запросу order by a.release_date (это поле находится в основной таблице фильмов) то запрос опять выполняется от 1 до 10 сек, при этом я поставил индекс на поле a.release_date
подскажите, пожалуйста, - как быть? Или на уровне БД тут уже ничего не оптимизировать и лучше все результаты записывтаь в массив а потом сортировтаь по ключу даты?

-~{}~ 13.03.06 03:01:

Ещё вопрос по поводу структуры таблицы связей:

сейчас страктура такая:

movie_id | genre_id

нужно ли добавить поле 'relateID' и сделать его PRIMARY? не знаю зачем это делается и как это поможет делу, но в некоторых движках встречал такую структуру таблицы связей.

 

[zerkms]

Spear
прочитай соответствующий раздел - там описываются ситуации - когда индексы используются при сортировке, а когда - нет

 

[Spear]

zerkms
я, к сожалению, не могу найти где почитать-то

-~{}~ 13.03.06 04:56:

ещё хотел спросить - кол-во записей в таблице связей - 3 000 000 и более. Это нормально? (это я про таблицу которая связывает фильм и актеров).

 

[zerkms]

Spear
http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html

 

[Spear]

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

а что по поводу:

нужно ли добавить поле 'relateID' и сделать его PRIMARY?

кол-во записей в таблице связей - 3 000 000 и более. Это нормально?

запрос, который выполняется 0.025 сек - это нормально? Я, конечно, буду стараться кешировтаь самые тяжелые запросы, но все же просто интересно, является ли 0.025 сек нормальным показателем?

 

[Wicked]

1) с полем типа DATE все выглядит еще лучше:
where release_date BETWEEN '2006-01-01' AND '2006-03-31'

2) Отсюда у меня такой вопрос (буду очень благодарен за овтет) - почему так происходит? как вы правильно вычислили по какой таблице лучше группировать? По той, для которой делается условие where?

Не совсем. Скорее по той, которая должна идти первой в explain plan'е если опустить group by.

3) Такой вопрос - запрос, который выполняется 0.025 сек - это нормально? Я, конечно, буду стараться кешировтаь самые тяжелые запросы, но все же просто интересно, является ли 0.025 сек нормальным показателем?

Зависит от того, какое ты рассчитываешь получить время работы скрипта (например подходя с точки зрения юзабилити), какое суммарное время работы скрипта на самом деле, и является ли именно этот скрипт узким местом.

4) сдается мне, что все таки
LEFT JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id
и условие
WHERE b.genre_id = '4'
с точки зрения полезности мало совместимы. Если уж ты знаешь, что тебе нужны фильмы с genre_id = 4, то зачем тебе фильмы вообще без жанра?
Так что используй либо
LEFT JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id
WHERE b.genre_id = '4'
либо
INNER JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id

 

[zerkms]

Wicked
и b.genre_id тоже в ON можно было бы добавить

 

[Spear]

подскажите, пожалуйста, в чем отличие left join от inner join?

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

До этого я использовал left join всегда, когда мне в запросе нужно было объединить таблицы но не обязательно в БД бывает соответствующая запись в подключаемой таблице.

поэтому мне наверное и сложно разобраься в запросах, т.к. не знаю чем отличается inner от left join

-~{}~ 13.03.06 07:22:

[sql]
select
a.movie_id as movie_id,
a.url as url_link,
c.title as title,
b.genre_id as janr,
d.r_date as r_date
from
ig20_movies_main as a
inner join ig20_movie2genres as b on a.movie_id=b.movie_id and b.genre_id='4'
inner join ig20_movies_titles as c on a.movie_id=c.movie_id
inner join ig20_movies_dates as d on a.movie_id=d.movie_id
group by b.movie_id order by d.r_date desc limit 0,200
[/sql]
explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref genre_id genre_id 4 const 11279 Using where; Using index; Using temporary; Using f...
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 ig.b.movie_id 1
1 SIMPLE c ref movie_id movie_id 4 ig.a.movie_id 2
1 SIMPLE d ref movie_id movie_id 4 ig.a.movie_id 2 Using index

___
то есть всеравно если добавляю order by то запрос выполняется долго.

-~{}~ 13.03.06 07:40:

__
ну все, теперь я вообще перестал понимать как работает БД.. смотрите6
сейчас если добавить к этому запросу ORDER BY d.r_date DESC то запрос выполняется долго (от 1 секунды)

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

То есть теперь запрос выглядит как:
[sql]
SELECT a.movie_id AS movie_id, a.url AS url_link, c.title AS title, b.genre_id AS janr, d.r_date AS r_date
FROM ig20_movies_main AS a
INNER JOIN ig20_movie2company AS b ON a.movie_id = b.movie_id AND b.company_id = '1117'
INNER JOIN ig20_movies_titles AS c ON a.movie_id = c.movie_id
INNER JOIN ig20_movies_dates AS d ON a.movie_id = d.movie_id
GROUP BY b.movie_id
ORDER BY d.r_date DESC
LIMIT 0 , 200
[/sql]
но этот запрос выполняется ровно столько же, сколько и без ORDER'a, то естб - 0,026 сек в среднем. Я попробовал задать сортировку по названию - order by c.title и запрос вновь выполнился мгновено. Это что, мистика получается? два абсолютно идентичных запроса за исключением 1 таблицы,
но ведь структуры и объемы тех таблиц тоже одинаковы, и индексы стоят так же. В чем может быть "косяк"?

 

[Wicked]

а у этих двух идентичных запросов идентичен ли explain plan?

-~{}~ 13.03.06 08:03:

zerkms
и b.genre_id тоже в ON можно было бы добавить

А смысл? с точки зрения полезности мало совместимы. Если уж ты знаешь, что тебе нужны фильмы с genre_id = 4, то зачем тебе фильмы вообще без жанра?

 

[Spear]

Wicked

а у этих двух идентичных запросов идентичен ли explain plan?

я поспешил с выводами - в таблице связей фильм-жанр - 1 500 000 записеЙ, в таблице связей "фильм-компания" - 600 000 записей.

Ещё такой вопрос.
Помните, почти в самом начале я говорил что буду записывать все жанры к которым относится фильм ещё и в поле genres основной таблицы фильмов.
Это я хотел делать чтобы уменьшить кол-во запросов в БД.
Например, во многих местах будут выводиться различная информация о том или ином фильме, и чтобы для каждого из них не делать запрос в таблицу связей "фильм-жанр" я и хочу оставить это поле, которое через запятую будет перечислять все фильмы.

Мне сказали что так делать не надо, но я так и не понял - почему.
Вот вообщем 3 конкретных вопроса, чтобы не писать ещё кучу текста и не отнимать лишний время у тех, кто мне помогает:

1. Если не дхранить для кжадого фильма все жанры в одном месте (в таблице фильмов основной), а использовать записи из таблицы связей то придется делать так:
до вывода информации о любом фильме в любом месте сайта нужно сначала собрать в массив айдишники всех фильмов, информация по которым будет показана.
Потом делаю один запрос в таблицу связей "жанр-фильм" и строю массив - перечисление всех жанров для каждого фильма. Массив будет не очень маленький (например, 300 фильмов в среднем по 3-4 жанра на штуку).
стоит ли так делать?

2. исходя из вопроса 1 - не лучше ли будет все-таки хранить дополнительно информацию о жанрах фильма в таблице фильмов (через запятую). Эта запись будет использована только для вывода. Поиск фильмов конкретного жанра бдет осуществляться при помощи таблицы связи "фильм-жанр".

какой из этих двух вариантов, как вы считаете, будет рыботать быстрее.
учитывая что:
расчитывается 250 000 записей в таблице фильмов
и максимум под 2 миллиона записей в таблице связей "фильм-жанр".

3. самый простой вопрос - какое же-таки использовать "сравнение" для полей varchar в которых перечилсяются все жнры по данному фильму через запятую? сейчас стоит "cp1251_general_ci" (по умолчанию).

 

[chira]

если решил хранить информацию о жанрах дополнительно в отдельном поле, то храни не список ID через запятую, а сами названия жанров.

 

[Spear]

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

 

[Wicked]

такой длинный пост, а на мой вопрос так и не ответил

я поспешил с выводами - в таблице связей фильм-жанр - 1 500 000 записеЙ, в таблице связей "фильм-компания" - 600 000 записей.
оно не должно замедлять запрос в 100 раз. сравни эксплэины.

теперь соображения по поводу жанров.

Да, идея с кэшированием списка id не такая уж и плохая, если этот кэш никогда не использовать в для joinов. С другой стороны такой comma separated список id жанров можно составлять на лету используя group_concat().

chira
а с тобой я не согласен

 

[chira]

я не претендую на статус своего предложения как единственно правильное

 

[Spear]

Wicked
я опять почистил БД, т.к. решил немного изменить стурктуру. Как только тестовая БД запонлится снова заполнится данными - выложу 2 експлейна.

-~{}~ 15.03.06 23:53:

Уважаемые специалисты! Появилась ещё такая проблемка:
не могу придумать как вытащить все фильмы не по одному жанру, а по нескольким.
То есть вытащить из БД айди фильмов, укоторых в таблице связть-жанр есть связть с категориями 1, 2 и 3, например.
Есть, конечно, один вариант, но я очень надеюсь что он не единственный.
Идея такова:
получаем от пользователя запрос на поиск фильма по жанрам 1,2 и 3. Сначала выбираем из базы айди всех фильмов по 1 жанру.
потом вторым запросом выбираем фильмы по второму жанру, чей movie_id in(перечисление айди фильмов аолученных первым запросом), и так далее.
Итого любой желающий может взять и задать поиск, указав совпадение 50-ти жанров\поджанров, таким образом выполнив 50 ресурсоемких запросов в БД (в таблице связей много записей, предполагаемый минимум - 1,5 млн.)
Подскажите, пожалуйста, ведь наверняка есть более удобный способ, т.к. иначе преимущества реляционных БД в данном конкретном случае я не вижу.

 

[Wicked]

[sql]SELECT
*,
count(distinct b.genre_id) as genres_cnt /* выбираем число связей ... */
FROM ig20_movies_main AS a
INNER JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id AND b.genre_id in (1, 2, 3) /* ... с жанрами 1, 2, 3 ... */
INNER JOIN ig20_movies_titles AS c ON a.movie_id = c.movie_id
INNER JOIN ig20_movies_dates AS d ON a.movie_id = d.movie_id
GROUP BY b.movie_id
HAVING genres_cnt = 3 /* ... и берем только те, у которых было 3 из 3 совпадений. */
ORDER BY d.r_date DESC[/sql]

 

[zerkms]

Wicked
AND b.genre_id
IN ( 1, 2, 3 ) /* ... с жанрами 1, 2, 3 ...
...
HAVING genres_cnt = 3/

а чем
AND b.genre_id = 1 AND b.genre_id = 2 AND ....
не мил??

 

[Wicked]

AND'ами

 

[zerkms]

Wicked
))
всё равно запрос генерируется php - не думаю что сделать AND будет сложнее или затратнее чем IN

 

[Wicked]

1) ты видимо совсем смысл запроса не понял.

2) b.genre_id = 1 AND b.genre_id = 2 как минимум подразумевает, что 1 = 2, а это есть ложь.