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

[Spear]

zerkms
по поводу 1:М, я просто хочу обяснить вот что:
я сделаю обязательно, НО всеравно останется поле, перечисляющее все жанры. Для чего?

вот прото пример:
вывести 200 фильмов + дату выхода + жанры, к которым относится фильм.
если я оставлю поле с перечислением жанров то это все будет делать 1 ПРОСТЫМ запросом:

select genres, date, title from movies

а если убрать это поле, то придется сначала выбирайть айди всех фильмов, которые будут отображены пользователю (ведь он их сортировать может),
потом выбрать категории из таблицы связей для каждого фильма, построить массив и так далее. Зачем? у меня будет поле в котором все жанры уже описаны. Благо, места это много не занимает - БД с 250к фильмов знимает около 70кб.

в таблица 1:м будет использоваться для поиска фильмов в жанре.

-~{}~ 12.03.06 07:52:

Andreika
хм, спасибо, идейка интересная,
только немного непонял как вычислять что

62100 - 1 квартал 06 etc

 

[Andreika]

еще раз попрошу - покажи запрос, который "задыхается" и в котором не используется LIKE, а исключительно 1:M

-----

если 34 цифры справа <=12 - это месяцы, от 20 - 21- первый квартал, 22 - второй... вытаскивать эти цифры простыми арифметическими операциями - делением / целочисленным делением / нахождением остатки (есть и в php и mysql)

 

[zerkms]

НО всеравно останется поле, перечисляющее все жанры

совсем не согласен

PS: а как фильм может принадлежать нескольким жанрам одновременно? (например в начале ты приводил пример с четырмя жанрами)

 

[Andreika]

zerkms
комедия/мелодрама )

 

[zerkms]

вытаскивать эти цифры простыми арифметическими операциями - делением / целочисленным делением / нахождением остатки

либо substr и SUBSTRING соответственно

 

[Spear]

Drama / Horror / Thriller / Remake
например.

-~{}~ 12.03.06 08:03:

совсем не согласен

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

 

[zerkms]

Spear
я тебе уже дважды предлагал сделать 1:М, андрейка просил показать запрос с 1:М

так что я как был не согласен с твоим высказыванием - так и не согласен до сих пор

 

[Spear]

да я запрос напишу и покажу, БД ещё не заполнена.

-~{}~ 12.03.06 08:35:

сейчас напшу запрос 1:М но пока такая проблема:

довольно простой ззарос выполняется 23 секунды:

[sql]
select a.movie_idvas movie_id, a.url as url_lnk, b.title as movie_name from movies_main as a, movies_titles as b where a.movie_id=b.movie_id group by(a.movie_id) limit 0,50
[/sql]

НО если убрать "group by(a.movie_id)" то выполняется за 0.0059 сек.

в чем проблема? подскажите пожалуйста.

пс.
кол-ва запсей в таблице фильмов - 250 000,
в таблице названий фильмов - 400 000

-~{}~ 12.03.06 08:39:

хм. если в предыдущем запросе группировать не по a.movie_id а по b.movie_id (т есть по айдишникам в таблице названий) то зпрос выполняется моментально.
Почему так происходит? Хот, не очень-то и важно.

 

[Wicked]

нда... выполни такой запрос:

[SQL]EXPLAIN SELECT a.movie_idvas movie_id, a.url AS url_lnk, b.title AS movie_name
FROM movies_main AS a
INNER JOIN movies_titles AS b on (a.movie_id = b.movie_id)
GROUP BY a.movie_id
LIMIT 0 , 50[/SQL]

... и запости сюда результат.

Оригинальный запрос немного модифицирован, но это не должно сказаться на скорости (разве что скобки в group by потенциально могли мешать использовать индексы, т.к. mysql считал это функцией от поля).

-~{}~ 12.03.06 08:59:

хм. если в предыдущем запросе группировать не по a.movie_id а по b.movie_id (т есть по айдишникам в таблице названий) то зпрос выполняется моментально.
Почему так происходит? Хот, не очень-то и важно.

Видимо потому что в этом случае могут быть использованы индексы для группировки?

 

[Spear]

не возвращает вообще никаких реультатов,
убрал explain - вывело.

-~{}~ 12.03.06 09:09:

Видимо потому что в этом случае могут быть использованы индексы для группировки?

но ведь у меня индексы стоит и там и там. (a.movie_id, b.movie_id) но группировка по первому тормозит, по второму работает нормально

пс
сделал запрос испльзуя 1:м, скорость выполнение - 0,6 - 0,7 то есть очень медленно. сейчас запосчу запрос

-~{}~ 12.03.06 09:15:

мда, я тоько сейчас понял что explain нужно через PHPMYADMIN юзать.

во результаты (по запросу от Wicked)

id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE b ALL movie_id NULL NULL NULL 500000
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 test.b.movie_id 1

-~{}~ 12.03.06 10:35:

*****************************************

РЕЗУЛЬТАТЫ тестов и запросы.
(первый запрос - структура таблиц "Один ко многим").

[sql]
select
a.movie_id as movie_id,
a.url as url_link,
c.title as title,
b.genre_id as janr,
d.day as day, d.month as month, d.year as year
from
ig20_movies_main as a,
ig20_movies_titles as c
left join ig20_movies_dates as d on a.movie_id=d.movie_id
left join ig20_movie2genres as b on a.movie_id=b.movie_id
where b.genre_id='4' and a.movie_id=b.movie_id and a.movie_id=c.movie_id group by a.movie_id limit 0,200
[/sql]
время выполнения: 1.65746


[sql]
select
a.movie_id as movie_id,
a.url as url_link,
c.title as title,
d.day as day, d.month as month, d.year as year
from
ig20_movies_main as a,
ig20_movies_titles as c
left join ig20_movies_dates as d on(a.movie_id=d.movie_id)
where a.genre like '%,4,%' and c.movie_id=a.movie_id group by a.movie_id limit 0,200
[/sql]
время выполнения: 0.05561 !!!!

получается запрос like '%$var%' быстрее реляционных таблиц? Блин, странно как-то.

Ещё раз повторю записей в основной таблице фильмов - 250000,
в таблице названий - 500 000,
в таблице "фильм-категория" - полтора миллиона

 

[Фанат]

но ведь у меня индексы стоит и там и там.

индексы должны не стоять.
индексы должны РАБОТАТЬ
ты результат EXPLAIN своего реляционного запроса смотрел?

 

[zerkms]

Spear
теперь показывай на каких полях каких таблиц у тебя стоят индексы
и смени

LEFT JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id

на

INNER JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id

 

[Spear]

Забыл указать важную деталь:
еслив первом запосе убрать GROUP BY a.movie_id то запрос выполняется за 0.015 сек, то есть быстрее чем запрос с LIKE'ом. Мистика... )

-~{}~ 12.03.06 10:53:

zerkms
индексы в каждой таблице на полях movie_id

смени

LEFT JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id

на

INNER JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id

пробовал, результат тот же (

 

[Wicked]

1) есть ли какой-то смысл в left join'ах?
2) есть ли смысл дублировать условия on в where?
3) алиасы таблицам ты подобрал ужасные
4) мой вариант:
таблица ig20_movie2genres должна имметь индекс (genre_id, movie_id).

[SQL]SELECT a.movie_id AS movie_id, a.url AS url_link, c.title AS title, b.genre_id AS janr, d.day AS
DAY , d.month AS
MONTH , d.year AS year
FROM ig20_movies_main AS a
INNER JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id
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
WHERE b.genre_id = 4
GROUP BY b.movie_id /* можно попробовать group by b.genre_id, b.movie_id,
чтобы использовать составной индекс - но не уверен,
что есть смысл*/
LIMIT 0 , 200[/SQL]

Пояснение: если уж есть условие b.genre_id = 4, то стоит дать шанс мусклю начать выборку именно с таблицы b. Далее таблицы a, c, d могут джоиниться в любой последовательности - они находятся уже в равных условиях.

От тебя жду explain'а этого запроса

 

[zerkms]

[SQL]
SELECT a.movie_id AS movie_id, a.url AS url_link, c.title AS title, b.genre_id AS janr, d.day AS
DAY , d.month AS
MONTH , d.year AS year
FROM ig20_movies_main AS a, ig20_movies_titles AS c
LEFT JOIN ig20_movies_dates AS d ON a.movie_id = d.movie_id
LEFT JOIN ig20_movie2genres AS b ON a.movie_id = b.movie_id
WHERE b.genre_id = '4' AND a.movie_id = b.movie_id AND a.movie_id = c.movie_id
GROUP BY a.movie_id
LIMIT 0 , 200
[/SQL]

все условия объединений вынеси в ON:

FROM ig20_movies_main AS a, ig20_movies_titles AS c

замени на соответствующее ему INNER JOIN

WHERE b.genre_id = '4' AND a.movie_id = b.movie_id AND a.movie_id = c.movie_id - это что?????

добавь индекс на movie2genres.genre_id

и как тебе уже не раз говорили - научись читать то что тебе говорит EXPLAIN

 

[Wicked]

ПС: вроде оптимизатор должен справиться с переделкой джоина всех таблиц к таблице а на джоин их к b.

 

[Spear]

Wicked
zerkms
Спасибо за ответы, я только сейчас опять до форум добрался.
Сейчас испробую все варианты.

по поводу:

есть ли какой-то смысл в left join'ах

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

-~{}~ 12.03.06 23:21:

вариант (от Wicked'a)

Хост: localhost
БД: ig
Время создания: Мар 12 2006 г., 22:12
Созданный: phpMyAdmin 2.6.1 / MySQL 4.1.8-max
SQL-запрос:
[sql]explain select a.movie_id as movie_id, a.url as url_link, c.title as title,
b.genre_id as janr, d.day as day, d.month as month, d.year as year from ig20_movies_main
as a inner join ig20_movie2genres as b on a.movie_id=b.movie_id innerjoin 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 where b.genre_id='4' group by b.movie_id limit 0,200;
[/sql]
Ряды: 4

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref genre_id genre_id 4 const 18574 Using where; Using index
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

на "деле" (то есть без explain) запрос выполнился за 0.025 сек.
(до этого запрос выполнялся от 1,5 до 2 секунд)
Ещё такой момент - если группировать не по b.movie_id а по c.movie_id, a.movie_id, d.movie_id то запрос выпонляется долго (до 2 сек).
Отсюда у меня такой вопрос (буду очень благодарен за овтет) - почему так происходит? как вы правильно вычислили по какой таблице лучше группировать? По той, для которой делается условие where?
Ещё одно дополнение - запись в таблице ig20_movies_titles - есть всегда (минимуам одна для каждого фильма). Стоит её джойнить или нет? Если я правильно понимаю, то джойн замедляет выполнение запроса?

 

[SelenIT]

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

 

[Spear]

Wicked
zerkms
огромное спасибо з вашу помощь, за то что так долго мучались со мною и моими запросами к БД!

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


ещё появился вопрос по структуре.
Как вы уже заметили, чтобы вытащить дату выхода фильма на экран я делаю запрос в таблицу релизов и доставю три поля - год, месяц и день.
Сделал я это для того, чтобы потом было удобно реализовать выборку, скажем, всех фильмов за такой-то год или за такой-то год и месяц. Приемлима ли такая реализация?
дело в том что пришла ещё такая идейка (после совета Andreika):

хранить дату в поле mediumint в таком формате
20060312 - 12 марта 2006 года.
Тогда, например, если мне понадобится выбрать все фильмы за 2006 год условие в запросе будет примерно такое:

where release_date >20060000 and release_date < 20070000

если выбрать фильмы за марта, то так:
where release_date >20060300 and release_date < 20060400

за 1 квартал 2006 года:
where release_date >20060100 and release_date < 20060400

Подскажите пожалуйста - как жевсе-таки лучше, учитывая что звписей в таблице релизов будет довольно много, то я стараюсь сделать структуру БД максимально оптимальной для запросов.
Кстате, если использовать этот вариант то нужно будет сделатьтолько 1 индекс на поле release_date, иначе придется делать минимум 3 индекса - на поля day, month, year.

 

[Фанат]

хранить дату в поле mediumint в таком формате

скажи, а ты никогда не интересовался - нет ли в мускуле специальных типов полей для даты?