Как правильно писать запросы для поиска по базе (MySQL) и не ронять сервер?

Question

[Xrist1An]

У меня есть база в которой около 70000 записей - музыка. Каждый трек относится к каким-то определенным жанрам.

Если песню можно отнести только к одному жанру (пусть будет trance), то для ее поиска я бы написал запрос через php следующего вида:

$result = mysql_query("SELECT * FROM music WHERE genre = 'trance'");

В поле genre для этого трека есть всего одно слово - trance, поэтому вроде как вполне быстро можно сделать выборку по всем таким трекам.

А вот если одна и так же композиция относится сразу к нескольким жанрам (пусть будет trance, house, electrohouse), то я пишу вот так:

$result = mysql_query("SELECT * FROM music WHERE genre LIKE '%trance%'");

Так как в поле genre для этого трека теперь уже 3 слова (trance, house, electrohouse), то приходится делать выборку через LIKE. Это уже ощутимо грузит сервер.

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

Заранее спасибо!

UPD:

С изначальным вопросом я все понял, всем спасибо!

Теперь еще вопрос:

Если человек вводит запрос вида: "trance and house", то как правильно делать в таком случае выборку, чтобы в результате были выведены все треки, в жанрах которых встречаются слова из этого запроса? В данном случае должны вывестись все треки из жанра house и trance.

$result = mysql_query("SELECT * FROM xref_music WHERE genre_id = 7 OR genre_id = 5");

Цифры 7 и 5 - это соответственно id жанров trance и house.

Т.е. я беру строку trance and house, разбиваю ее по словам. А дальше? Как-нибудь убрать из нее все лишнее (and) и оставить только существующие жанры (house, trance)?

Потом загнать каждый жанр по отдельности в запрос как я показал в своем примере? Это по феншую будет?

Answer 1

[Дмитрий Энтелис]

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

genre
---
genre_id
genre_title

Заводим табличку для указания соответствий

xref_music_genre
---
music_id
genre_id

Все.

UPD

Если человек вводит запрос вида: "trance and house", то как правильно делать в таком случае выборку, чтобы в результате были выведены все треки, в жанрах которых встречаются слова из этого запроса? В данном случае должны вывестись все треки из жанра house и trance.

select music_id 
from xref_music_genre 
where genre_id IN (5,7) 
group by music_id 
HAVING count(genre_id)=2

Comments

[evnuh]

Xrist1An: ещё посмотрите тип поля ENUM, но это имеет свои ограничения и хуже чем 3НФ

[Xrist1An]

Правильно ли я понимаю, что в xref_music_genre будут повторения?

Т.е. выглядеть это может так:

music_id | genre_id
5 | 1
5 | 4
5 | 7
7 | 3

Трек под номером 5 может соответствует жанру под номером 1,4 и 7. Все верно?

[Микола]

Xrist1An: Да. SELECT * FROM xref_music_genre WHERE genre_id = 7; вернет ID всех песен где есть жанр с ID 7.

[Xrist1An]

Микола: Храни вас бог)

[Xrist1An]

Спасибо за апдейт в ответе.

"HAVING count(genre_id)=2"

Какую задачу выполняет эта строчка?

[Микола]

Если без GROUP BY то HAVING равно WHERE

[Микола]

Xrist1An: по сути здесь проверка чтобьі бьіло 2 жанра. т.к. where genre_id IN (5,7) может вернуть тока 1 жанр (5,7) а данньій код проверяет 2=2.

[Xrist1An]

Микола: Понял, спасибо

[Дмитрий Энтелис]

Xrist1An: я все таки верю что Вы присядите и почитаете про sql запросы.
На поверхностном уровне можно разобраться за пару дней легко.

[Дмитрий Энтелис]

Микола: Xrist1An: having не равно where!
having это условие которое применяется ПОСЛЕ групировки.

Т.е как работает этот запрос по шагам:

select music_id 
from xref_music_genre 
where genre_id IN (5,7)

возвращает набор связей
music_id, genre_id
1, 5
2, 7
3, 5
3, 7

Нам нужны песни у которых есть оба жанра - т.е нам нужны песни у которых есть 2 связи.
делаем group by music_id держа в голове неявно count(genre_id)
music_id, count(genre_id)
1, 1
2, 1
3, 2
ну и применяем HAVING отбрасывая лишнее

[Xrist1An]

Дмитрий Энтелис: спасибо за развернутый ответ)

Answer 2

[FanatPHP]

Дополню предыдущий ответ несколькими замечаниями.

Во-первых, сам запрос

SELECT m.* FROM genre g, music_genre mg,  music m
WHERE g.name = 'trance' AND mg.genre_id=g.id AND m.id=mg.music_id

Во-вторых, никаких mysql_query, только PDO. Код должен выглядеть так:

$sql = "SELECT m.* FROM genre g, music_genre mg,  music m
WHERE g.name = ? AND mg.genre_id=g.id AND m.id=mg.music_id";
$stmt = $pdo->prepare($sql);
$stmt->execute([$search]);
$allmusic = $stmt->fetchAll();

В-третьих, обе колонки в music_genre должны быть проиндексированы. или даже так - два индекса, один из которых "music_id, genre_id", а второй - "genre_id, music_id"
Ну и на genre.name тоже можно повесить, уникальный

Comments

[Xrist1An]

К сожалению, совершенно не понимаю, что значит "m.*". Что за буква m? Почему точка? Ваш запрос для меня как китайский язык) Где об этом можно почитать и узнать поподробнее? Короче говоря, какое ключевое слово для поиска?

mysqli - это PDO? Если буду писать запросы таким способом, то это приемлимо?

[FanatPHP]

1. это алиас имени таблицы. по этому слову и искать. 2. mysqli - это не PDO. Писать тем способом, который у тебя в посте неприемлемо. Тот способ, который у меня - приемлемый. Иначе бы я его не писал. Главный принцип, который ты должен всегда соблюдать: Ни одна переменная не должна попадать в запрос напрямую, а ТОЛЬКО через плейсхолдер.

[Евгений]

а чего не join?

[FanatPHP]

Nc_Soft: видишь здесь джойн? И я нет. А он есть!

[Евгений]

FanatPHP: :D

[Дмитрий Авилов]

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

[FanatPHP]

Дмитрий Авилов: это про PDO замечание?

Answer 3

[Xrist1An]

С изначальным вопрос я все понял, всем спасибо!

Теперь еще вопрос:

Если человек вводит запрос вида: "trance and house", то как правильно делать в таком случае выборку, чтобы в результате были выведены все треки, в жанрах которых встречаются слова из этого запроса? В данном случае должны вывестись все треки из жанра house и trance.

$result = mysql_query("SELECT * FROM xref_music WHERE genre_id = 7 OR genre_id = 5");

Цифры 7 и 5 - это соответственно id жанров trance и house.

Т.е. я беру строку trance and house, разбиваю ее по словам. А дальше? Как-нибудь убрать из нее все лишнее (and) и оставить только существующие жанры (house, trance)?

Потом загнать каждый жанр по отдельности в запрос как я показал в своем примере? Это по феншую будет?

Comments

[Дмитрий Энтелис]

напишите отдельный вопрос или обновите существующий

[Xrist1An]

Точно, да.

Answer 4

[Дмитрий Авилов]

А вообще, если не собираетесь часто менять жанры, лучше всего использовать поле типа SET, оно для того и создано, чтобы хранить определенное количество фиксированных значений. Создавать для этого отдельную таблицу, мне кажется, перебор. И потом с помощью оператора FIND_IN_SET() можно делать любые выборки, поставив индекс на это поле всё будет выбираться мгновенно.

Comments

[Дмитрий Авилов]

Например, пошёл у вас запрос, показать песни с жанрами house И trance. Вы, получив эти два слова, просто вбиваете в запрос "SELECT * FROM music where FIND_IN_SET(genre, "house") > 0 AND FIND_IN_SET(genre, "trance") > 0 Это по фен-шую и очень быстро (в случае, если вы не меняете список имеющихся жанров, а вы его, кажется, не собираетесь менять, да даже если и поменяете - ничего страшного не случится, просто нужно будет структуру таблицы чуть поменять, добавив/удалив значение)

[Xrist1An]

Дмитрий Авилов: Спасибо большое, очень нужная информация!