Идентичные запросы отрабатывают странно. надо расставить индексы.

camka · 19 Июл 2004

Идентичные запросы отрабатывают странно. надо расставить индексы.

Имеется две таблицы.

Код:

CREATE TABLE `stat_search` (
  `id` int(11) NOT NULL default '0',
  `site` int(11) NOT NULL default '0',
  `engine` int(11) NOT NULL default '0',
  `query` int(11) NOT NULL default '0',
  `hits` int(11) NOT NULL default '0',
  `visits` int(11) NOT NULL default '0',
  `clients` int(11) NOT NULL default '0',
  KEY `query_id` (`query`,`id`),
  KEY `id` (`id`,`site`)
) TYPE=MyISAM PACK_KEYS=1 

CREATE TABLE `r_search` (
  `id` int(11) NOT NULL auto_increment,
  `k` varchar(255) binary default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `k` (`k`),
  KEY `id_k` (`id`,`k`)
) TYPE=MyISAM

первая - статистика поиска слов для отдельных сайтов по дням. query - ссылка на айди слова во второй таблице
вторая - сами слова

делаю запрос

Код:

mysql> select r.k, sum(hits) as totalhits from stat_search s inner join r_search r on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and r.id = 3 group by s.id order by 1 desc ;
+-------+-----------+
| k     | totalhits |
+-------+-----------+
| delfi |      2250 |
| delfi |      2167 |
+-------+-----------+
2 rows in set (3.71 sec)

mysql> select r.k, sum(hits) as totalhits from stat_search s inner join r_search r on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and s.query = 3 group by s.id order by 1 desc ;
+-------+-----------+
| k     | totalhits |
+-------+-----------+
| delfi |      2250 |
| delfi |      2167 |
+-------+-----------+
2 rows in set (0.02 sec)

здесь надо обратить внимание на время выполнения каждого запроса

а теперь, соответсевенно эксплейны

Код:

mysql> explain select r.k, sum(hits) as totalhits from stat_search s inner join r_search r on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and r.id = 3 group by s.id order by 1 desc ;
+-------+-------+---------------+----------+---------+-------+------+-----------------+
| table | type  | possible_keys | key      | key_len | ref   | rows | Extra           |
+-------+-------+---------------+----------+---------+-------+------+-----------------+
| r     | const | PRIMARY,id_k  | PRIMARY  |       4 | const |    1 | Using temporary |
| s     | ref   | query_id,id   | query_id |       4 | const |    5 | Using where     |
+-------+-------+---------------+----------+---------+-------+------+-----------------+
2 rows in set (0.01 sec)


mysql> explain select r.k, sum(hits) as totalhits from stat_search s inner join r_search r on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and s.query = 3 group by s.id order by 1 desc ;
+-------+-------+---------------+----------+---------+---------+------+----------------------------------------------+
| table | type  | possible_keys | key      | key_len | ref     | rows | Extra                                        |
+-------+-------+---------------+----------+---------+---------+------+----------------------------------------------+
| s     | range | query_id,id   | query_id |       8 | NULL    |    6 | Using where; Using temporary; Using filesort |
| r     | ref   | PRIMARY,id_k  | id_k     |       4 | s.query |    1 | Using index                                  |
+-------+-------+---------------+----------+---------+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

судя по эксплейнам, просматриваемых рядов достаточно мало. вопрос - почему в первом запросе мускул не оптимизирует запрос до вида второго запроса, ведь в таблице r_search он найдет только одну запись соответствующую условию, используя индекс PRIMARY id.

-~{}~ 19.07.04 15:59:

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

Код:

mysql> select r.k, sum(hits) as totalhits from stat_search s use index (id) inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
+-------+-----------+
| k     | totalhits |
+-------+-----------+
| delfi |      2250 |
| delfi |      2167 |
+-------+-----------+
2 rows in set (0.04 sec)


mysql> explain select r.k, sum(hits) as totalhits from stat_search s use index (id) inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20040601 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
+-------+-------+---------------+------+---------+-------+------+-------------+
| table | type  | possible_keys | key  | key_len | ref   | rows | Extra       |
+-------+-------+---------------+------+---------+-------+------+-------------+
| r     | const | PRIMARY,k     | k    |     256 | const |    1 |             |
| s     | range | id            | id   |       8 | NULL  | 4573 | Using where |
+-------+-------+---------------+------+---------+-------+------+-------------+
2 rows in set (0.01 sec)

значит ли это, что нельзя доверять explain на 100%?

Falc · 19 Июл 2004

camka
>>вопрос - почему в первом запросе мускул не оптимизирует запрос до вида второго запроса

Насколько я понимаю мускул такого делать не умеет.

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

попробуй сделать индекс: site,query,id

camka · 19 Июл 2004

странно. при увеличении периода в between (причем, совершенно незначительномвновь торможение возобновляется

Код:

mysql> select r.k, sum(hits) as totalhits from stat_search s use index (id) inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20040401 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
...
63 rows in set (2.49 sec)

сделал индекс на site, query, id

Код:

mysql> select r.k, sum(hits) as totalhits from stat_search s  inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20020401 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
...
759 rows in set (3.45 sec)

mysql> select r.k, sum(hits) as totalhits from stat_search s use index(site_query_id) inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20020401 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
...
759 rows in set (0.11 sec)

mysql> explain select r.k, sum(hits) as totalhits from stat_search s use index(site_query_id) inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20020401 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
+-------+-------+---------------+---------------+---------+-------+------+-----------------+
| table | type  | possible_keys | key           | key_len | ref   | rows | Extra           |
+-------+-------+---------------+---------------+---------+-------+------+-----------------+
| r     | const | PRIMARY,k     | k             |     256 | const |    1 | Using temporary |
| s     | range | site_query_id | site_query_id |      12 | NULL  | 1460 | Using where     |
+-------+-------+---------------+---------------+---------+-------+------+-----------------+
2 rows in set (0.00 sec)

mysql> explain select r.k, sum(hits) as totalhits from stat_search s  inner join r_search r  on s.query = r.id where site in ( 4) and s.id between 20020401 and 20040602 and r.k = 'delfi' group by s.id order by 1 desc ;
+-------+-------+---------------------------+----------+---------+-------+------+-----------------+
| table | type  | possible_keys             | key      | key_len | ref   | rows | Extra           |
+-------+-------+---------------------------+----------+---------+-------+------+-----------------+
| r     | const | PRIMARY,k                 | k        |     256 | const |    1 | Using temporary |
| s     | ref   | query_id,id,site_query_id | query_id |       4 | const |    5 | Using where     |
+-------+-------+---------------------------+----------+---------+-------+------+-----------------+
2 rows in set (0.00 sec)

опять надо указывать явно, какой индекс пользовать

причем, когда запрос с use index выполняется первый раз, время его выполнения намного дольше, чем остальные разы... чего не было замечено с другими запросами

Falc · 19 Июл 2004

camka
Я понял в чем твоя проблема. У меня было тоже самое, можешь почитать:
http://phpclub.ru/talk/showthread.php?s=&threadid=41984&highlight=explain

Мне с этим справиться не удалось

camka · 19 Июл 2004

запрос оказался до нельзя нестабильным. я потер индекс query_id и поставил составной на (site, query, id). use index убрал. таким образом достигается наименьшее время выполнения. однако, когда запрос выполняется первый раз, время его выполнения оставляет желать лучшего. еще такой вопрос - почему в составном индексе именно такой порядок следования полей?

Falc · 19 Июл 2004

camka
>>почему в составном индексе именно такой порядок следования полей?

Про порядок полей в ключе можно почитать в мане.

Для данного запроса важно чтобы поле на которое накладывается диапазонное условие было в конце запроса.

camka · 19 Июл 2004

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

-~{}~ 20.07.04 09:42:

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

Код:

mysql> select count(*) from stat_search where id between 20040101 and 20040601 and site=4;
+----------+
| count(*) |
+----------+
|    13074 |
+----------+
1 row in set (1.93 sec)

mysql> show indexes from stat_search;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| stat_search |          1 | id            |            1 | id          | A         |         786 |     NULL | NULL   |      | BTREE      |         |
| stat_search |          1 | id            |            2 | site        | A         |       24838 |     NULL | NULL   |      | BTREE      |         |
| stat_search |          1 | site_query_id |            1 | site        | A         |          59 |     NULL | NULL   |      | BTREE      |         |
| stat_search |          1 | site_query_id |            2 | query       | A         |      596125 |     NULL | NULL   |      | BTREE      |         |
| stat_search |          1 | site_query_id |            3 | id          | A         |     2384500 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> explain select count(*) from stat_search where id between 20040101 and 20040601 and site=4;
+-------------+------+------------------+---------------+---------+-------+--------+--------------------------+
| table       | type | possible_keys    | key           | key_len | ref   | rows   | Extra                    |
+-------------+------+------------------+---------------+---------+-------+--------+--------------------------+
| stat_search | ref  | id,site_query_id | site_query_id |       4 | const | 286416 | Using where; Using index |
+-------------+------+------------------+---------------+---------+-------+--------+--------------------------+
1 row in set (0.01 sec)

mysql> select count(*) from stat_search use index (id)where id between 20040101 and 20040601 and site=4;
+----------+
| count(*) |
+----------+
|    13074 |
+----------+
1 row in set (3.58 sec)


mysql> explain select count(*) from stat_search use index (id)where id between 20040101 and 20040601 and site=4;
+-------------+-------+---------------+------+---------+------+--------+--------------------------+
| table       | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+-------------+-------+---------------+------+---------+------+--------+--------------------------+
| stat_search | range | id            | id   |       8 | NULL | 630681 | Using where; Using index |
+-------------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.02 sec)

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

вот еще результаты
сделал индекс (site, id) по совету Фалка, чтоб диапазонная часть в конце была. похоже, что она опять не используется при выборке потому как, при незначительном увеличении периода в between условии время выполнения опять значительно увеличивается.

Код:

mysql> select count(*) from stat_search where id between 20040101 and 20040601 and site=4;
+----------+
| count(*) |
+----------+
|    13074 |
+----------+
1 row in set (0.08 sec)

mysql>  select count(*) from stat_search where  site=4 and id between 20030101 and 20040601 ;
+----------+
| count(*) |
+----------+
|   187997 |
+----------+
1 row in set (2.00 sec)

Falc · 20 Июл 2004

camka
>>вот вам и подтверждение тому, что составной индекс глючит при диапазонном условии

Об этом я и писал в приведеном выше топике.

>>и как вообще проверяется, какие части индекса используются а какие нет?

Определяется по полю "key_len" а эксплайне.

Идентичные запросы отрабатывают странно. надо расставить индексы.

camka

не самка

Falc

Новичок

camka

не самка

Falc

Новичок

camka

не самка

Falc

Новичок

camka

не самка

Falc

Новичок