Меню
Что нового?

Странная проблема с MySQL

grey109

Новичок
Странная проблема с MySQL

Есть две таблицы:

create table cd (
cd_id integer not null primary key,
artist_name text not null,
album_name text,
year integer default null,
length_cd text default null,
genre_id integer default null,
links text default null,
comments text default null
);

create table tracks (
cd_id integer not null,
track_no integer not null,
title_track text not null,
length_track text default null,
PRIMARY KEY (cd_id, track_no)
);


Я хочу организовать по ним поиск. Для этого я даю такой запрос:

SELECT cd.cd_id, cd.artist_name, cd.album_name, tracks.cd_id, tracks.track_no, tracks.title_track, tracks.length_track FROM cd, tracks WHERE (tracks.title_track Like '%май%' OR cd.artist_name Like '%май%' OR cd.album_name Like '%май%') AND cd.cd_id=tracks.cd_id ORDER BY tracks.title_track


И тут начинается самое интересное:
1. дикие тормоза - этот запрос выполняется около секунды на 700 Дюроне, хотя были и другие запросы превосходящие по сложности кострукии и поразмерам этот в 3-4 раза и они выполнялись 3-8 сотых секунды.
2. если заменить ORDER на GROUP, то запрос выполняется как и положено, т.е. 2-4 сотых секунды. Но вся проблема в том что мне нужно именно ORDER.
3. под 98 Виндой все pаботает отлично, не считая тоpмозов. Под 2000 Виндой, на той же самой машине, с теме же веpсиями apacha, php и mysql, получаем ошибку:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\www\search.php on line 175

175 стpока это:

while (list ($cd_id, $artist_name, $album_name, $cd_id, $track_no, $title_track, $length_track) = mysql_fetch_row($res))

Какого??? Повтоpюсь, что все одинаковое, pазница только в ОС.


У кого-нибудь есть какие-нибудь мысли в чем может быть причина тормозов и не работы запросо под 2000 Виндой?

PS: php - 4.2.2, mysql - 3.23.49
 

Доктор

Новичок
Почему тормоза при ордер шайтан знает. Хотя в любом случае по tittle_track стоит создать индекс.
А вот "supplied argument is not a valid MySQL result resource" практическиоднозначно указывает, что запрос не выполнился - ошибка в запросе или еще чего, так как запрос у тебя, кажись, правильный. Что за "еще чего" даже предположить не возьмусь. При такой фигне у себя попробовалбы в первую очередь тупо переустановить MySQL.
 

grey109

Новичок
Делал индексы так:

CREATE INDEX cd ON cd (cd_id, artist_name(100), album_name(100), length_cd(10), year, genre_id, links(50), comments(150));
CREATE INDEX tracks ON tracks (cd_id, title_track(150), track_no, length_track(10));
CREATE INDEX genre ON genre (genre_id, genre_name(150));

или так:

CREATE INDEX cd ON cd (cd_id);
CREATE INDEX tracks ON tracks (cd_id);


Скорость выборки выросла в два раза, т.е. время сократилось с 0,8 сек. до 0,4 сек. Хорошо, но все равно медленно.

Вот ответ по "explain select" (чтобы таблица выглядела более или менее нормально, вместо пробелов я поставил точки)

table...type..possible_keys...key.....key_len..ref.......rows..Extra
cd......ALL...PRIMARY,cd......NULL....NULL.....NULL......382...Using temporary; Using filesort
tracks..ref...PRIMARY,tracks..tracks..4........cd.cd_id..14....where used

Если не сложно, прокомментируйте данные из этой таблицы.


PS: пока еще не переустанавливал mysql под 2000-ой, так ничего нового сказать не могу.
 

Апельсин

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

1. поиск идет по условию '%искомая_подстрока%' - т.е. индексы не используются. В 4й версии они другой алгоритм для подобного поиска используют, что немного увеличивает скорость.
2. у тебя есть OR :)
 

Доктор

Новичок
Индекс нужно создать отдельный у tracks по title_track, чтобы ускорить order by
 

su1d

Старожил PHPClubа
Попробуй вместо запуска кучи регэкспов сделать весь поиск одним:

WHERE CONCAT(tracks.title_track, ' ', cd.artist_name, ' ', cd.album_name) LIKE '%май%'
 

grey109

Новичок
Попробуй вместо запуска кучи регэкспов сделать весь поиск одним:

WHERE CONCAT(tracks.title_track, ' ', cd.artist_name, ' ', cd.album_name) LIKE '%май%'
Нажмите для раскрытия...
Дело в том что от запрос генерируется автоматически в зависимости от количества слов. Тогда получается запро будет выглядеть так:

WHERE (CONCAT(tracks.title_track, ' ', cd.artist_name, ' ', cd.album_name) LIKE '%ласковый%' ) AND (CONCAT(tracks.title_track, ' ', cd.artist_name, ' ', cd.album_name) LIKE '%май%')...

Я прав?
 

RomikChef

Guest
match aganist должен, вроде, ускорить поиск.
 

мшеул

Guest
Только теория, проверять лень

При LIKE '%...%' индексы не используются.
Поэтому индекс на title_track повлияет только на ORDER BY, что тоже
в принципе неплохо. Только ограничь его по длине (8-ми символов я думаю
достаточно будет).

ALTER TABLE ADD INDEX title_track (title_track(8)),

> CREATE INDEX cd ON cd (cd_id);
> CREATE INDEX tracks ON tracks (cd_id);
(я не понял как пользоваться Quote)

Индекс для cd_id не нужен, потому как он уже есть (PRIMARY KEY)

> CREATE INDEX cd ON cd (cd_id, artist_name(100), album_name(100), length_cd(10), year, genre_id, links(50), comments(150));
Не дай бог тебе в эту таблицу INSERT и UPDATE делать...
Длина индекса = 422 байта!!!

Да и судя по запросу - индекс бестолковый.
При использовании множественного индекса в условии поля из индекса ДОЛЖНЫ
следовать строго в том порядке в каком они объявлены в индексе.
А иначе толку нет. Он использоваться не будет.

Вот теперь подошли к основному. Есть такая особенность нормальных языков
программирования выполнять/проверять условия строго в порядке их следования.
Сделай так:

SELECT ...
FROM cd, tracks
WHERE cd.cd_id=tracks.cd_id
AND
(tracks.title_track Like '%май%'
OR cd.artist_name Like '%май%'
OR cd.album_name Like '%май%')
ORDER BY tracks.title_track

или

SELECT ...
FROM cd INNER JOIN tracks USING(cd_id)
WHERE
(tracks.title_track Like '%май%'
OR cd.artist_name Like '%май%'
OR cd.album_name Like '%май%')
ORDER BY tracks.title_track


table...type..possible_keys...key.....key_len..ref.......rows..Extra
cd......ALL...PRIMARY,cd......NULL....NULL.....NULL......382...Using temporary; Using filesort
tracks..ref...PRIMARY,tracks..tracks..4........cd.cd_id..14....where used

В таблице cd для 382 строк MySQL использует временные таблицы и filesort.
Это самое худшее, что можно придумать.

Сделай что я написал и запости результаты (вместе с explain).
И мне на мыло скинь, чтоб я посмотрел. А то я тут случайно проходил. И судя по всему больше и не буду...

Про FULLTEXT search и MATCH AGAINST помочь не смогу, т.к. ничего не знаю.
Но ходят слухи что он берёт только символы более 4-х символов.
Для преодоления этого препятствия надо перекомпилривать MySQL.


ТУПАЯ РЕГИСТРАЦИЯ
Вообще то я терпеливый человек но нестолько же.
10 минут на регистрацию.
И какая разница какой мой Location

Не, моей ноги здесь больше не будет.
 
tony2001

tony2001

TeaM PHPClub
>Не, моей ноги здесь больше не будет.
"вот уйду от вас, а вы потом плакать будете!"

(с) Апельсин =)
 

Апельсин

Оранжевое создание
tony, нехорошо цитировать личную переписку в форуме без согласия автора :)
кроме того еще и неточно цитировать! :)
 

grey109

Новичок
Автор оригинала: мшеул
в принципе неплохо. Только ограничь его по длине (8-ми символов я думаю
достаточно будет).
Нажмите для раскрытия...
А если сделать больше - это плохо? Если я правильно понял, ведь то слово которое я ищу может и не входить в эти 8 символов, в результатет толку не будет от создания индеса.


Не дай бог тебе в эту таблицу INSERT и UPDATE делать...
Длина индекса = 422 байта!!!
Нажмите для раскрытия...
Данные таблицы не изменются.

Вот теперь подошли к основному. Есть такая особенность нормальных языков
программирования выполнять/проверять условия строго в порядке их следования.
Сделай так:
Нажмите для раскрытия...
Вот это не знал...

table...type..possible_keys...key.....key_len..ref.......rows..Extra
cd......ALL...PRIMARY,cd......NULL....NULL.....NULL......382...Using temporary; Using filesort
tracks..ref...PRIMARY,tracks..tracks..4........cd.cd_id..14....where used

В таблице cd для 382 строк MySQL использует временные таблицы и filesort.
Это самое худшее, что можно придумать.
Нажмите для раскрытия...
А как сделать чтобы было лучше. И еще , не пойму откуда взялась цифра 14, треков сейчас большее чем 5000.

Сделай что я написал и запости результаты (вместе с explain).
И мне на мыло скинь, чтоб я посмотрел. А то я тут случайно проходил. И судя по всему больше и не буду...
Нажмите для раскрытия...
ОК. Я тоже вот случайно наткнулся на этот форум - очень доволен, море полезной инфы и умных людей. Теперь советую его друзьям-программерам.
 

grey109

Новичок
Автор оригинала: su1d
Попробуй вместо запуска кучи регэкспов сделать весь поиск одним:

WHERE CONCAT(tracks.title_track, ' ', cd.artist_name, ' ', cd.album_name) LIKE '%май%'
Нажмите для раскрытия...
Попробовал, но выигрыш составил всего 1-3 сотых секунды, которые вполне можно списать на погрешность.
 

grey109

Новичок
Автор оригинала: мшеул
ALTER TABLE ADD INDEX title_track (title_track(8)),
Нажмите для раскрытия...
Сделал.

Индекс для cd_id не нужен, потому как он уже есть (PRIMARY KEY)
Нажмите для раскрытия...
После того как убрал индеск по cd_id, скорость выборки упала в два раза. Ну да ладно...

программирования выполнять/проверять условия строго в порядке их следования.
Сделай так:

SELECT ...
FROM cd, tracks
WHERE cd.cd_id=tracks.cd_id
AND
(tracks.title_track Like '%май%'
OR cd.artist_name Like '%май%'
OR cd.album_name Like '%май%')
ORDER BY tracks.title_track
Нажмите для раскрытия...
Сделал, но на результат не повлияло.

Теперь запрос выглядит так:

EXPLAIN SELECT cd.cd_id, cd.artist_name, cd.album_name, tracks.cd_id, tracks.title_track, tracks.length_track from cd, tracks where cd.cd_id=tracks.cd_id AND ((CONCAT(tracks.title_track, cd.artist_name, cd.album_name) LIKE '%фристайл%')) ORDER BY tracks.title_track ASC

Результат (сорри что не очень ровно):



table type possible_keys key key_len ref rows Extra
tracks ALL PRIMARY NULL NULL NULL 5504 Using filesort
cd eq_ref PRIMARY PRIMARY 4 tracks.cd_id 1 where used
 

chira

Новичок
в таблице traks у тебя намного больше записей чем в cd

что если попробовать так:

SELECT cd.cd_id, cd.artist_name, cd.album_name, tracks.cd_id, tracks.title_track, tracks.length_track
from cd INNER JOIN traks ON cd.cd_id = tracks.cd_id AND (CONCAT(cd.artist_name, cd.album_name) LIKE '%фристайл%')
where tracks.title_track LIKE '%фристайл%' ORDER BY tracks.title_track ASC
 

mahoune

Guest
Вот что удалось найти из доки! Может это может помочь...

MySQL 4.0 does another optimisation on LIKE. If you use ... LIKE "%string%" and string is longer than 3 characters, MySQL will use the Turbo Boyer-Moore algorithm to initialise the pattern for the string and then use this pattern to perform the search quicker.


In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column and partial indexing is not supported. See section 6.8 MySQL Full-text Search for details.


5.4.5 Multiple-Column Indexes

MySQL can create indexes on multiple columns. An index may consist of up to 15 columns. (On CHAR and VARCHAR columns you can also use a prefix of the column as a part of an index.)

A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.

MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you don't specify values for the other columns.

Suppose a table is created using the following specification:

mysql> CREATE TABLE test (
-> id INT NOT NULL,
-> last_name CHAR(30) NOT NULL,
-> first_name CHAR(30) NOT NULL,
-> PRIMARY KEY (id),
-> INDEX name (last_name,first_name));

Then the index name is an index over last_name and first_name. The index will be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index will be used in the following queries:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
-> AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
-> AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
-> AND first_name >="M" AND first_name < "N";

However, the name index will NOT be used in the following queries:

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
-> OR first_name="Michael";
 

grey109

Новичок
Автор оригинала: chira
в таблице traks у тебя намного больше записей чем в cd

что если попробовать так:

SELECT cd.cd_id, cd.artist_name, cd.album_name, tracks.cd_id, tracks.title_track, tracks.length_track
from cd INNER JOIN traks ON cd.cd_id = tracks.cd_id AND (CONCAT(cd.artist_name, cd.album_name) LIKE '%фристайл%')
where tracks.title_track LIKE '%фристайл%' ORDER BY tracks.title_track ASC
Нажмите для раскрытия...
Попробовал. Результат - ничего не найдено, хотя должно быть.


2mahoune: спасибо за доку, но это не подойдет т.к. я использую 3-ю версию.
 

chira

Новичок
да, не найдет если %фристайл% не встечается ни в artist_name ни в album_name

приведеный SQL не претендует на готовое решение , скорее это тема для размышлений
 
Войдите или зарегистрируйтесь для ответа.
Сверху