Спецам по оптимизации запросов вопрос

[NetScripter]

Спецам по оптимизации запросов вопрос

Всем привет.

Бьюсь над проблемой: выборка по индексу из таблицы размером 60 MB, 320,000 записей, занимет 1-2 секунды, что по моему мнению(и требованию с программной т. зрения) очень много.
Вот структура:




CREATE TABLE `cp_data` (
`id` int(11) NOT NULL auto_increment,
`times` timestamp NULL default CURRENT_TIMESTAMP,
`url` varchar(255) default NULL,
`referer` varchar(255) default NULL,
`ip` varchar(22) default NULL,
`visitor` varchar(255) default NULL,
`proj_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`),
KEY `times` (`times`),
KEY `proj_id` (`proj_id`),
KEY `visitor` (`visitor`),
KEY `fulli` (`times`,`proj_id`,`visitor`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=0 AUTO_INCREMENT=325593 ;

Запрос следующего вида:

SELECT *, WEEK(times) dow FROM cp_data WHERE visitor=1989 AND proj_id=4 AND times <= DATE_ADD(NOW(), INTERVAL -1 DAY)+0 AND times >= DATE_ADD(NOW(), INTERVAL -93 DAY)+0 GROUP BY dow

explain выдает possible_keys: times,proj_id,visitor,fulli
key: proj_id

Почему из возможных 4 ключей использует только один? Мне думается в этом причина тормозов.

Заранее благодарю

 

[SunDrop]

MySQL всегда использует ТОЛЬКО ОДИН ключ при выборке.
Если нужен ключ по нескольким полям - делай составной ключ!

 

[NetScripter]

ну так сделал, но он не хочет использовать составной ключ(fulli)

-~{}~ 25.03.06 17:29:

А вот попробовал удалить остальные индексы кроме fulli и он стал использовать индекс, правда скорость не шибко то увеличилась

 

[SunDrop]

И каким же это образом ты ключ создал?

 

[vGhost]

1) храни IP в INT ! работать будет быстрее (http://ru2.php.net/ip2long)
2) храни дату в INT по тем же причинам

А причина тормозов в не правильной последовательности следования полей в составном ключе.
Поэксперементируй с разной последовательностью полей в KEY `fulli` (`times`,`proj_id`,`visitor`) и сам поймёш разницу на том же EXPLAIN (смотри количество затронутых записей и количество реально удовлтворяющих условию)

 

[SunDrop]

Использование индексов в MySQL
Многостолбцовые индексы

-~{}~ 25.03.06 17:23:
vGhost

храни дату в INT по тем же причинам

Это еще зачем????

 

[vGhost]

Выборка по инт (unsigned) индексу быстрее работает..
(тэстировал)..

 

[NetScripter]

Сделал иначе, чтоб избежать лишних вычилений, добавил поле week_no(мне нужно вывести кол-во разных неделей), и создал индекс на нем, однако когда в запросе появляется AND visitor=37282 то индекс перестает использоваться, даже том случае если это поле есть в составе индекса key(week_no, visitor)
SELECT distinct week_no FROM cp_data WHERE visitor=59897;

 

[SunDrop]

Выборка по инт (unsigned) индексу быстрее работает..
(тэстировал)..

Именно тЭстировал? Ух ты. Каким образом? На каких запросах?

 

[NetScripter]

Добился использования индекса, но время около 0.7 с. Может это предел?

 

[SunDrop]

NetScripter

visitor=37282 то индекс перестает использоваться, даже том случае если это поле есть в составе индекса key(week_no, visitor)

И не должен. Работает правило крайнего левого. Т.е. создавая индекс (поле1, поле2, поле3, поле4) ты автоматически получаешь следующие индексы (поле1, поле2, поле3) + (поле1, поле2) + (поле1) и больше НИКАКИХ ДРУГИХ индексов.
По ссылкам ходил? Читал? В самом руководстве это искал?

 

[vGhost]

Если клониш к тому что мускуль хранит дату у себя внутри всё равно в int то попробуй потэстируй скорость выполнения запроса на базе в которой скажем будет 16м записей сделай выборку по составному индексу в котором дата участвует и тоже самое только дата записанная как unsigned int ..

 

[SunDrop]

Добился использования индекса, но время около 0.7 с. Может это предел?

А время каким образом засекаешь?
Вывод explain в студию!

 

[NetScripter]

На самом деле индекс используется, все ок. Правило крайнего левого работает. Но долго, вот схема еще раз:
CREATE TABLE `cp_data2` (
`id` int(11) NOT NULL auto_increment,
`times` timestamp NULL default CURRENT_TIMESTAMP,
`url` varchar(255) default NULL,
`referer` varchar(255) default NULL,
`ip` varchar(22) default NULL,
`visitor` varchar(255) default NULL,
`proj_id` int(11) NOT NULL,
`week_no` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `week_no` (`visitor`,`week_no`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 PACK_KEYS=0 AUTO_INCREMENT=326575 ;

SELECT distinct week_no FROM cp_data2 WHERE visitor=59812;
KEY: week_no
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------------------------------------+
| 1 | SIMPLE | cp_data2 | index | week_no | week_no | 259 | NULL | 326574 | Using where; Using index; Using temporary

засекаю обычным клиентом mysql, при быстрых запросах время 0.00, а тут 1.хх

 

[SunDrop]

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

Есть у меня под рукой база с табличками на 29 млн! записей.
Выборку сделать? КАКУЮ? Все ж от потребностей зависит. Выборка выборке рознь. И вообще - зачем придумали поле DATE, DATETIME и TIMESTAMP, если оказывается простое преобразование в int работает быстрее в ... ?

-~{}~ 25.03.06 18:36:

На самом деле индекс используется, все ок.

На самом деле ничего хорошего:

index
Данный тип аналогичен ALL, за исключением того, что просматривается только дерево индексов. Этот тип обычно более быстрый чем ALL, поскольку индексный файл, как правило, меньше файла данных.

В столбце rows указывается число строк, которые MySQL считает нужным проанализировать для выполнения запроса.

Не работает у тебя индекс так как нужно!
Синтаксис оператора EXPLAIN

 

[vGhost]

Автор оригинала: SunDrop
Есть у меня под рукой база с табличками на 29 млн! записей.
Выборку сделать? КАКУЮ? Все ж от потребностей зависит. Выборка выборке рознь.

Вот с этим согласен..

Автор оригинала: SunDrop
И вообще - зачем придумали поле DATE, DATETIME и TIMESTAMP, если оказывается простое преобразование в int работает быстрее в ... ?

А ты попробуй, и не int а unsigned int
Выборка по состаному ключу допустим (date, userid) где оба unsigned int и тоже самое когда первое datetime

 

[NetScripter]

ну и что делать то с этим индексом чтоб он работал как нужно?

 

[Andreika]

NetScripter
может не использовать VARCHAR в индексе? в запросе ты пишешь visitors=число без кавычек, но он у тебя почему-то VARCHAR(255) NULL .. нельзя с ним ничего сделать?

 

[NetScripter]

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

Спасибо всем, просто нужно было не VARCHAR, а INT