Лучший способ заполнить пробелы во временном ряду в запросе Mysql

Мне нужно заполнить пробелы во временном ряду набора результатов запроса mysql. Я нахожусь в процессе тестирования возможности выполнения внешнего соединения со вспомогательной таблицей, которая содержит все точки данных временного ряда (как указано в этом потоке: Как заполнить пробелы в дате в MySQL?).

Проблема, с которой я сталкиваюсь, заключается в том, что добавление этого соединения значительно увеличивает время ответа на запрос (оно увеличивается с менее 1 секунды до 90 секунд).

Вот исходный запрос:

select date_format(fact_data7.date_collected,'%Y-%m') as date_col
   , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
   , fact_data7.batch_id,fact_data7.value as fdvalue,entities.ticker as ticker
   , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
   , date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7  
JOIN entities on fact_data7.entity_id=entities.id  
where (1=1)
  AND ((entities.id= 963
      AND fact_data7.metric_id=1
      ))
  AND date_format(fact_data7.date_collected,'%Y-%m') > '2008-01-01'
order by date_col asc

а вот запрос с добавленным внешним соединением с вспомогательной таблицей (month_fill):

select date_format(month_fill.date,'%Y-%m') as date_col
    , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
    , fact_data7.batch_id,fact_data7.value as fdvalue
    , entities.ticker as ticker
    , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
    , date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7
JOIN entities
  on fact_data7.entity_id=entities.id  
RIGHT OUTER JOIN month_fill
   on date_format(fact_data7.date_collected,'%Y-%m') =  date_format(month_fill.date,'%Y-%m')  
where (1=1)
  AND (
      (entities.id= 963 AND fact_data7.metric_id=1)
      OR (entities.id is null and fact_data7.metric_id is null)
      )
  AND date_format(month_fill.date,'%Y-%m') > '2008-01-01'
order by date_col asc

Могу ли я изменить структуру запроса для повышения производительности? Есть ли альтернативное решение для достижения того, что я ищу?

Обновление от 15 ноября:

Вот вывод EXPLAIN для 1-го запроса:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  entities    const   PRIMARY     PRIMARY     4   const   1   Using filesort
1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where

Вот вывод EXPLAIN для 2-го запроса:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  month_fill  index   NULL    date    8   NULL    204     Using where; Using index; Using temporary; Using filesort
1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where
1   SIMPLE  entities    eq_ref  PRIMARY     PRIMARY     4   findata.fact_data7.entity_id    1   Using where

mysql sql database gaps-and-islands
person opike    schedule 14.11.2011    source источник
comment
Что, где результаты EXPLAIN?   -  person Conrad Frix    schedule 14.11.2011
comment
Добавил в первоначальный вопрос.   -  person opike    schedule 15.11.2011

Ответы (2)


arrow_upward
0
arrow_downward

Даже не глядя на рефакторинг запроса, я бы начал с добавления индекса в столбцы даты fact_data7.data_collected и month_fill.date. Запрос диапазона ">", который вы делаете, замедляет процесс, и добавление индекса теоретически должно повысить производительность, но вам нужно достаточно записей, иначе управление индексом замедлится только из-за обработки, связанной с управлением индексом.

См. эту документацию mysql http://dev.mysql.com/doc/refman/5.0/en/optimization-indexes.html

Я не совсем уверен, чего вы пытаетесь достичь, но вы можете попытаться сделать это, используя функцию ifnull(value1,value2) mysql. Ваш запрос может выглядеть примерно так:

select ifnull(date_format(fact_data7.date_collected,'%Y-%m'),date_format(month_fill.date,'%Y-%m')) as date_col, 
date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col, 
fact_data7.batch_id,
fact_data7.value as fdvalue,
entities.ticker as ticker,
date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2 ,
date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7 , month_fill
JOIN entities on fact_data7.entity_id=entities.id  
where ((entities.id= 963 AND fact_data7.metric_id=1) OR (entities.id is null and fact_data7.metric_id is null))
and date_format(fact_data7.date_collected,'%Y-%m') =  date_format(month_fill.date,'%Y-%m') --you will need a condition similar to this depends on the data
AND date_format(fact_data7.date_collected,'%Y-%m')>'2008-01-01'
order by date_col asc
person Stainedart    schedule 14.11.2011
comment
У меня уже есть индекс date_collected в fact_data7. Я просто добавил индекс в поле даты во вспомогательной таблице, но эта таблица относительно намного меньше (240 строк против ~ 20000 в fact_data7), и индекс не оказал никакого влияния на производительность. - person opike; 14.11.2011
comment
можете ли вы сделать подсчет по второму запросу со вторым соединением. Я подозреваю, что вы можете вернуть в 240 раз больше записей, чем ожидалось? - person Stainedart; 15.11.2011
comment
Это не декартово произведение, если вы об этом подозреваете. Первый запрос возвращает 18958 строк, а второй запрос возвращает 19055 строк (с заполненными пустыми месяцами). - person opike; 15.11.2011

arrow_upward
0
arrow_downward

Думаю, стоит попробовать переписать where, чтобы не использовать date_format(date_collected). Вы говорите, что у вас есть индекс для этого поля, но он никогда не используется (поле является аргументом функции, MySQL не поддерживает индексы на основе функций)

person a1ex07    schedule 15.11.2011