Форум пользователей MySQL

deadka · 11.04.2011 13:14:25

Доброго времени суток!

Поделитесь соображениями по сабжу плиз .

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

drop table if exists `weather`;

CREATE TABLE if not exists `weather` (`t` timestamp not null DEFAULT CURRENT_TIMESTAMP,`d` bigint(20) default NULL,key(t)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `weather` values

 ('2011-01-01',25)

,('2011-01-02',26)

,('2011-01-03',34)

,('2011-01-04',30)

,('2011-01-05',29)

,('2011-01-06',26)

,('2011-01-07',23)

,('2011-01-08',21)

,('2011-01-09',20)

,('2011-01-10',22);

Хотим найти максимальный перепад.
Через пользовательские переменные делается вполне быстро, сложность O(n).

set @i=NULL,@i1=NULL,@m=0;select t,d,@i1:=`d` `nextdata`,if (abs(@i1-@i)>@m,@m:=abs(@i1-@i),@m), @i:=d from `weather`;select @m;

В то же время, смущает вопрос отсутствия сортировки данных (по-хорошему ведь надо бы отсортировать перед тем, как делать проход, указанный выше).

Заход с другой стороны:

select max(abs(t1.d-t2.d)) from `weather` as t1 left join `weather` as t2 on (t1.t = t2.t + interval 1 day);

Отредактированно deadka (11.04.2011 13:15:02)

vasya · 11.04.2011 13:28:45

deadka написал:
Возникает вопрос - почему не использует ключ, и можно ли его заставить?

http://sqlinfo.ru/forum/viewtopic.php?pid=23966#p23966

deadka · 11.04.2011 15:12:32

vasya написал:
http://sqlinfo.ru/forum/viewtopic.php?pid=23966#p23966

Спасибо. То есть я инструкцией (t1.t = t2.t + interval 1 day) заставляю его проходить вычисления везде...

Тогда уточняющий вопрос - сию конструкцию можно вообще оптимально с join переписать?

Или только корелированным подзапросом (как ниже привёл)?, в нем нормально индекс используется.

select max(abs(t1.d-(select d from weather t2 where t2.t = t1.t - interval 1 day ))) from weather t1;

mysql> explain select max(abs(t1.d-(select d from weather t2 where t2.t = t1.t - interval 1 day ))) from weather t1;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t | t | 4 | func | 2 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

vasya · 11.04.2011 17:06:39

deadka написал:
Тогда уточняющий вопрос - сию конструкцию можно вообще оптимально с join переписать?

Можно сделать ещё одно поле, хранящее разницу с предыдущим днем, и обойтись простейшим запросом
А через join оптимально на мой взгляд не выйдет.

deadka написал:
Или только корелированным подзапросом (как ниже привёл)?, в нем нормально индекс используется.

select max(abs(t1.d-(select d from weather t2 where t2.t = t1.t - interval 1 day ))) from weather t1;

mysql> explain select max(abs(t1.d-(select d from weather t2 where t2.t = t1.t - interval 1 day ))) from weather t1;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t | t | 4 | func | 2 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Что-то не соображу, почему в зависимом запросе показывает rows = 2?

vasya · 11.04.2011 17:38:28

deadka написал:
set @i=NULL,@i1=NULL,@m=0;select t,d,@i1:=`d` `nextdata`,if (abs(@i1-@i)>@m,@m:=abs(@i1-@i),@m), @i:=d from `weather`;select @m;

В то же время, смущает вопрос отсутствия сортировки данных (по-хорошему ведь надо бы отсортировать перед тем, как делать проход, указанный выше).

А если сделать FORCE INDEX t, разве это не гарантирует проход в правильном направлении?

deadka · 12.04.2011 17:14:10

vasya написал:
Что-то не соображу, почему в зависимом запросе показывает rows = 2?

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

vasya написал:
deadka написал:
set @i=NULL,@i1=NULL,@m=0;select t,d,@i1:=`d` `nextdata`,if (abs(@i1-@i)>@m,@m:=abs(@i1-@i),@m), @i:=d from `weather`;select @m;

В то же время, смущает вопрос отсутствия сортировки данных (по-хорошему ведь надо бы отсортировать перед тем, как делать проход, указанный выше).
А если сделать FORCE INDEX t, разве это не гарантирует проход в правильном направлении?

С такой выборкой он вообще отказывается этот ключ видеть...

 CREATE TABLE `t_4104_rand` (

  `t` timestamp NOT NULL default CURRENT_TIMESTAMP,

  `d` bigint(20) default NULL,

  KEY `t` (`t`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `t_4104_rand` VALUES 

('2011-01-08 18:00:00',20),

('2011-01-06 18:00:00',23),

('2011-01-04 18:00:00',29),

('2011-01-02 18:00:00',34),

('2010-12-31 18:00:00',25),

('2011-01-01 18:00:00',26),

('2011-01-09 18:00:00',22),

('2011-01-03 18:00:00',30),

('2011-01-07 18:00:00',21),

('2011-01-05 18:00:00',26);

. А можно как-то?

Отредактированно deadka (12.04.2011 17:16:19)

paulus · 12.04.2011 21:27:07

Брр, какие у вас интересные изыскания А я — <strike>каждой бочке затычка</strike>
тоже встряну, пожалуй

Зачем в изначальном сообщении левое объединение? Оно там по смыслу не нужно
Далее — можно воспользоваться внешними знаниями, которые не доступны MySQL.
Например, отличное внешнее знание — каждая дата встречается в таблице только один
раз. Небольшое напряжение мозгов, чтобы попытаться объяснить MySQL, что это
значит на его языке (кстати, rows = 2 могло бы служить тут подсказкой, что MySQL
не понимает, что строка одна).

Код:

[silentia] root test > alter table weather drop index t, add primary key (t);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

[silentia] root test > EXPLAIN SELECT MAX(ABS(t1.d - t2.d)) FROM weather t1, weather t2 WHERE t1.t = t2.t + INTERVAL 1 day;
+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL |   10 |             |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

deadka · 12.04.2011 23:33:04

paulus написал:
Брр, какие у вас интересные изыскания

Ага, настолько интересные, что тебя аж передернуло .

Спасибо за подсказку насчет primary key. unique index дал тот же эффект.

Кстати говоря, после того, как я подействовал на табличку ANALYZE TABLE'ом, количество rows даже при неуникальном индексе стало равно 1, type как был ref так и остался.

Код:

mysql> explain select max(abs(t1.d-(select d from t_4104 t2 where t2.t = t1.t - interval 1 day ))) from t_4104 t1;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
|  2 | DEPENDENT SUBQUERY | t2    | ref  | t             | t    | 4       | func |    2 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> ANALYZE TABLE t_4104;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sqlinfo.t_4104 | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> explain select max(abs(t1.d-(select d from t_4104 t2 where t2.t = t1.t - interval 1 day ))) from t_4104 t1;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
|  2 | DEPENDENT SUBQUERY | t2    | ref  | t             | t    | 4       | func |    1 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Так что возвращаясь к

vasya написал:
Что-то не соображу, почему в зависимом запросе показывает rows = 2?

то видимо, статистика индексов была неактуализированная, а после analyze table исправилась. Спасибо разделу "Оптимизация простых запросов" .

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

vasya написал:
deadka написал:
set @i=NULL,@i1=NULL,@m=0;select t,d,@i1:=`d` `nextdata`,if (abs(@i1-@i)>@m,@m:=abs(@i1-@i),@m), @i:=d from `weather`;select @m;

В то же время, смущает вопрос отсутствия сортировки данных (по-хорошему ведь надо бы отсортировать перед тем, как делать проход, указанный выше).
А если сделать FORCE INDEX t, разве это не гарантирует проход в правильном направлении?

Отредактированно deadka (12.04.2011 23:42:05)

paulus · 15.04.2011 20:45:55

Честно говоря, не понял, в чем вопрос. ORDER BY тут нет, повода для индекса
не видно

deadka · 17.04.2011 22:15:59

Это я все в контексте #5 (Васино замечание) не пойму .

Но в любом случае - индекс ведь иногда используется и без сортировки и where. вот например:

CREATE TABLE `t_4104_rand` (

  `t` timestamp NOT NULL default CURRENT_TIMESTAMP,

  `d` bigint(20) default NULL,

  UNIQUE KEY `t` USING BTREE (`t`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `t_4104_rand` VALUES 

 ('2011-01-08 18:00:00',20)

,('2011-01-06 18:00:00',23)

,('2011-01-04 18:00:00',29)

,('2011-01-02 18:00:00',34)

,('2010-12-31 18:00:00',25)

,('2011-01-01 18:00:00',26)

,('2011-01-09 18:00:00',22)

,('2011-01-03 18:00:00',30)

,('2011-01-07 18:00:00',21)

,('2011-01-05 18:00:00',26);

Данные неупорядочены.

mysql> explain select t from t_4104_rand;
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_4104_rand | index | NULL | t | 4 | NULL | 10 | Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select t from t_4104_rand;
+---------------------+
| t |
+---------------------+
| 2011-01-01 00:00:00 |
| 2011-01-02 00:00:00 |
| 2011-01-03 00:00:00 |
| 2011-01-04 00:00:00 |
| 2011-01-05 00:00:00 |
| 2011-01-06 00:00:00 |
| 2011-01-07 00:00:00 |
| 2011-01-08 00:00:00 |
| 2011-01-09 00:00:00 |
| 2011-01-10 00:00:00 |
+---------------------+
10 rows in set (0.00 sec)

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

paulus написал:
Честно говоря, не понял, в чем вопрос. ORDER BY тут нет, повода для индекса
не видно

Ну вот тут тоже нет order by, однако повод для индекса он успешно находит .

В то же время если выбирать не только t, но и еще что-то, то картина совсем другая.

mysql> select t,d from t_4104_rand;
+---------------------+------+
| t | d |
+---------------------+------+
| 2011-01-09 00:00:00 | 20 |
| 2011-01-07 00:00:00 | 23 |
| 2011-01-05 00:00:00 | 29 |
| 2011-01-03 00:00:00 | 34 |
| 2011-01-01 00:00:00 | 25 |
| 2011-01-02 00:00:00 | 26 |
| 2011-01-10 00:00:00 | 22 |
| 2011-01-04 00:00:00 | 30 |
| 2011-01-08 00:00:00 | 21 |
| 2011-01-06 00:00:00 | 26 |
+---------------------+------+
10 rows in set (0.00 sec)

mysql> explain select t,d from t_4104_rand;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_4104_rand | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

И форс индекса ничего не даст, данные идут как в таблицу записаны.

Почему так получается?

В то же время, если создать двойной индекс.

mysql> alter table t_4104_rand add index(t,d);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> select t,d from t_4104_rand;
+---------------------+------+
| t | d |
+---------------------+------+
| 2011-01-01 00:00:00 | 25 |
| 2011-01-02 00:00:00 | 26 |
| 2011-01-03 00:00:00 | 34 |
| 2011-01-04 00:00:00 | 30 |
| 2011-01-05 00:00:00 | 29 |
| 2011-01-06 00:00:00 | 26 |
| 2011-01-07 00:00:00 | 23 |
| 2011-01-08 00:00:00 | 21 |
| 2011-01-09 00:00:00 | 20 |
| 2011-01-10 00:00:00 | 22 |
+---------------------+------+
10 rows in set (0.00 sec)

mysql> explain select t,d from t_4104_rand;
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_4104_rand | index | NULL | t_2 | 13 | NULL | 10 | Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

то опять он же - без всякого order by он все успешно сортирует и без просьбы.

Так почему он отказывается выбирать данные по индексу, если выбираем t,d, а индекс есть только на t?

Отредактированно deadka (17.04.2011 22:21:21)

paulus · 18.04.2011 16:48:37

Using index — это упоминание о том, что база не будет обращаться к данным,
достаточно данных в индексе (индекс является «накрывающим» для запроса).

Оптимизация чрезвычайно полезна для табличек MyISAM, которые кэшируют
индексы в памяти (в отличие от данных), а потому можно выдать результат
сильно быстрее. Если же надо выбрать колонку, которой нет в индексе, например,
прийдется по индексу бежать за данными (и тогда может оказаться, что проще
бежать за данными не по индексу, а полным сканом таблицы).

deadka · 18.04.2011 22:59:01

Спасибо!

Форум пользователей MySQL

#1 11.04.2011 13:14:25

оптимизация запроса с join

#2 11.04.2011 13:28:45

Re: оптимизация запроса с join

deadka написал:

#3 11.04.2011 15:12:32

Re: оптимизация запроса с join

vasya написал:

#4 11.04.2011 17:06:39

Re: оптимизация запроса с join

deadka написал:

deadka написал:

#5 11.04.2011 17:38:28

Re: оптимизация запроса с join

deadka написал:

#6 12.04.2011 17:14:10

Re: оптимизация запроса с join

vasya написал:

vasya написал:

deadka написал:

#7 12.04.2011 21:27:07

Re: оптимизация запроса с join

Код:

#8 12.04.2011 23:33:04

Re: оптимизация запроса с join

paulus написал:

Код:

vasya написал:

vasya написал:

deadka написал:

#9 15.04.2011 20:45:55

Re: оптимизация запроса с join

#10 17.04.2011 22:15:59

Re: оптимизация запроса с join

paulus написал:

#11 18.04.2011 16:48:37

Re: оптимизация запроса с join

#12 18.04.2011 22:59:01

Re: оптимизация запроса с join

Board footer