Интерпретация объяснения и предложений MySQL для увеличения времени ответа на запрос

Не могли бы вы перечислить результат SHOW CREATE TABLE?

Также не могли бы вы попробовать этот вариант и посмотреть, сколько времени это займет:

SELECT  
    data_date,  
    sum(closeprice)  
FROM moving_avgs  
INNER JOIN  
(  
    SELECT distinct symbol  
    FROM moving_avgs  
    WHERE    
        ma200_close     >= 5.00 and    
        ma200_volume    >= 400000 and  
        data_date   =  
        (  
            SELECT min(data_date)  
            FROM moving_avgs  
            WHERE year(data_date) = 2007  
        )   
) symbols ON symbols.symbol = moving_avgs.symbol  
GROUP BY data_date;  

Подозреваю три источника медлительности (в совокупности или по отдельности). Обоснование первых двух довольно просто:

(1) Индексы в вашей таблице могут быть разработаны не так хорошо, как могли бы быть. Я не вижу хорошего использования индекса в вашей информации EXPLAIN.

(2) То, как спроектирован подзапрос в WHERE, может вынуждать движок не использовать индекс, который у вас есть для «символа» - потеря производительности, которую в противном случае индекс мог бы дать вам. Вывод EXPLAIN делает вид, что это потеря.

(3) Еще один способ сформулировать (2), не упоминая список индексов, состоит в том, что машина, возможно, неэффективно работает с основным подзапросом (подзапросом в WHERE) на основе неправильного вывода отношения к внешнему запросу (т.е. считает, что существует связь — что ваш запрос является коррелированным подзапросом — и делает неверный выбор в отношении этой связи).

[Примечание. То, как написано ваше WHERE, подзапрос НЕ является коррелированным запросом, и он может быть выполнен эффективно, а IN может быть разрешен относительно эффективно (хотя, возможно, без индекса); тем не менее, механизм может неправильно интерпретировать эту ситуацию - у вас действительно есть несколько сложная ситуация с вложенным подзапросом, из-за которой механизм может ошибиться].

В любом случае, перемещение подзапроса в объединение может исправить такой сценарий, поскольку исключает любую возможность того, что механизм попытается неэффективно связать подзапрос с остальной частью вашего запроса. Когда подзапрос является источником для соединения, механизм должен разрешить его, прежде чем можно будет рассмотреть остальную часть тела вашего запроса. Это устраняет любые неверные выводы о взаимосвязи между подзапросом и остальной частью запроса, которые может делать движок.

Я подозреваю, что это условие:

(select min(data_date) from moving_avgs
                            where year(data_date) = 2007)

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

Если на data_date есть индекс, вы можете разрешить MySQL использовать индекс, изменив его на

(select min(data_date) from moving_avgs
where data_date between '01-01-2007' and '12-31-2007')

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

1) создав и установив две переменные my.ini:

max_heap_table_size = 256M

tmp_table_size = 512M

И, 2) увеличение третьей переменной:

myisam_sort_buffer_size = 256M

3) удаление трех индексов с одним полем и замена его индексом с четырьмя полями (INDEX: data_date-ma200_close-ma200_volume-symbol)

Мне удалось сократить время до 178 секунд.

Наряду с 4) благодаря @DWright, благодаря реструктуризации запроса, теперь он сократился до 67 секунд.

Один из способов, который я вижу, - предварительно вычислить min(data_date) для каждого года. Таким образом, вам не нужно запускать запрос SELECT для каждой записи внешнего запроса. Но тогда вам нужно будет поддерживать эту таблицу, чтобы гарантировать, что она всегда имеет минимальную дату_даты для данного года в любой момент времени.