Кстати введения...
Я столкнулся с этим вопросом:Разница между двумя соседними полями -Дата -PHP MYSQL и пытался достичь цели, то есть перебрать даты и получить разницу с чистым MySQL.
Еще один вопрос(Вычитание одной строки данных из другой в SQL)помогло мне понять, как сделать что-то подобное с MySQL. Это не решило проблему, так как решения по-прежнему зависят либо от фиксированных значений, либо от предполагаемого порядка данных, но это помогло мне понять методологию.
Есть еще один вопрос(Как получить следующую/предыдущую запись в MySQL?)с ответами, описывающими, как получить значения из следующей/предыдущей строки. Это все еще зависит от некоторых фиксированных значений, но я научился использовать эту технику.
Скажем, у меня есть эта таблицаfoo
:
CREATE TABLE `foo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dateof` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
id | dateof
-----+------------
1 | 2012-01-01
2 | 2012-01-02
11 | 2012-01-04
12 | 2012-01-01
13 | 2012-01-02
14 | 2012-01-09
111 | 2012-01-01
112 | 2012-01-01
113 | 2012-01-01
Есть два предположения:
id
)упорядочен по возрастанию и разрешены "дыры".dateof
действительна, в значении :нет NULL
с и нет значений по умолчанию(0000-00-00
). Я хочу перебрать каждую строку и подсчитать количество дней, прошедших с предыдущей записью, чтобы получить это: id | date | days_diff
-----+------------+-----------
1 | 2012-01-01 | 0
2 | 2012-01-02 | 1
11 | 2012-01-04 | 2
12 | 2012-01-01 | -3
13 | 2012-01-02 | 1
14 | 2012-01-09 | 7
111 | 2012-01-01 | -8
112 | 2012-01-01 | 0
113 | 2012-01-01 | 30
Со всем, что я узнал, я пришел к этому решению (, скажем решение 1 , так как есть другое):
SELECT
f.id,
DATE_FORMAT(f.dateof, '%b %e, %Y') AS date,
(SELECT DATEDIFF(f.dateof, f2.dateof)
FROM foo f2
WHERE f2.id = (
SELECT MAX(f3.id) FROM foo f3 WHERE f3.id < f.id
)
) AS days_diff
FROM foo f;
(пример скрипки здесь:http://sqlfiddle.com/#!2/099fc/3).
Это работает как шарм... до тех пор, пока в db не останется всего пара записей. Хуже становится, когда больше:
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f ALL NULL NULL NULL NULL 17221
2 DEPENDENT SUBQUERY f2 eq_ref PRIMARY PRIMARY 4 func 1 Using where
3 DEPENDENT SUBQUERY f3 index PRIMARY PRIMARY 4 NULL 17221 Using where; Using index
18031 строк :продолжительность:8,672 сек. Выборка:228,515 сек.
Я подумал о добавлении индекса в dateof
столбце:
CREATE TABLE `foo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dateof` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dateof` (`dateof`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
...и получил небольшое улучшение:
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f index NULL dateof 4 NULL 18369 Using index
2 DEPENDENT SUBQUERY f2 eq_ref PRIMARY PRIMARY 4 func 1 Using where
3 DEPENDENT SUBQUERY f3 index PRIMARY dateof 4 NULL 18369 Using where; Using index
18031 строк :продолжительность:8,406 сек.Выборка:219,281 сек.
Я вспомнил, что где-то читал о преимуществах MyISAM перед InnoDB в некоторых случаях. Поэтому я изменил на MyISAM:
ALTER TABLE `foo` ENGINE = MyISAM;
18031 строк :продолжительность:5,671 сек. Выборка:151,610 сек.
Конечно, стало лучше, но все равно медленно.
Я пробовал с другим алгоритмом(решение 2):
SELECT
f.id,
DATE_FORMAT(f.dateof, '%b %e, %Y') AS date,
(SELECT DATEDIFF(f.dateof, f2.dateof)
FROM foo f2
WHERE f2.id < f.id
ORDER BY f2.id DESC
LIMIT 1
) AS days_diff
FROM foo f;
...но он был еще медленнее:
18031 строк :продолжительность:15,609 сек. Выборка:184,656 сек.
Существуют ли другие способы оптимизации этого запроса или структуры данных, чтобы эта задача выполнялась быстрее?