MySQL Slow on join and order by

Question

I have a slow query which join 4 tables and with order by. It will take +30 Seconds.

I have index on GuruList.GuruName, GuruList.id, GuruName , Stocks.TickerName , Stocks.exchange, triple_insider.symbol, triple_insider.exchange, triple_insider.date

This is the query :

SELECT DISTINCT stock_list.symbol as t, 
                stock_list.exchange,
                REPLACE(stock_list.company, '    ', ' ') as c,
                REPLACE(triple_insider.position, '    ', ' ') as p,
                triple_insider.date as d,
                triple_insider.name as n,
                triple_insider.type as y,
                triple_insider.trans_share as r,
                triple_insider.cost as cs,
                triple_insider.price as z,
                stock_list.price as x,
                ROUND(100*(stock_list.price-triple_insider.price)/triple_insider.price, 1) as h
    FROM  triple_insider 
        LEFT JOIN stock_list 
            ON triple_insider.symbol=stock_list.symbol 
                AND triple_insider.exchange=stock_list.exchange  
        LEFT JOIN Stocks 
            ON triple_insider.symbol=Stocks.TickerName 
                AND triple_insider.exchange=Stocks.exchange  
        LEFT JOIN GuruList 
            ON Stocks.GuruName=GuruList.GuruName    
    WHERE stock_list.price > 0  
        AND stock_list.mktcap >= 100 
        AND stock_list.rank_balancesheet/10 >= 5 
        AND stock_list.volume != 0 
        AND stock_list.volume >= 200000 
        AND stock_list.price >= 2 
        AND stock_list.price <= 10 
        AND stock_list.shares != 0 
        AND stock_list.shares <= 500 
        AND stock_list.p_pct_change != 0 
        AND stock_list.p_pct_change >= 2 
        AND stock_list.cash2debt >0 
        AND stock_list.cash2debt >= 0.1 
        AND stock_list.equity2asset >0 
        AND stock_list.equity2asset >= 0.1 
        AND stock_list.fscore != 0 
        AND stock_list.fscore >= 1 
        AND stock_list.zscore != 0 
        AND stock_list.zscore >= 0 
        AND stock_list.medpsvalue > 0 
        AND stock_list.p2medpsvalue <= 0.7 
        AND stock_list.p2iv_dcf_share <= 0.5 
        AND  GuruList.id IN( 0,155 ,88 ,54 ,11 ,47 ,112 ,84 ,3 ,20 ,22 ,114 ,67 ,40 ,102 ,164 ,50 ,64 ,108 ,163)  
        AND stock_list.exchange IN ('NAS','NYSE','OTCPK','','OTCBB','AMEX')   
    ORDER BY triple_insider.date DESC
    LIMIT 5001

This is the Explain:

-------------------------------------------+
| id | select_type | table          | type   | possible_keys                                                                      | key      | key_len | ref                                                                       | rows | Extra                                        |
+----+-------------+----------------+--------+------------------------------------------------------------------------------------+----------+---------+---------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | GuruList       | range  | PRIMARY,GuruList_GuruName,GuruList_id,GuruName                                     | PRIMARY  | 4       | NULL                                                                      |   20 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Stocks         | ref    | GuruName,TickerName,exchange,exchange_2                                            | GuruName | 43      | g_main.GuruList.GuruName                                                  | 1490 | Using where                                  |
|  1 | SIMPLE      | triple_insider | ref    | triple_insider_symbol_index,exchange                                               | exchange | 22      | g_main.Stocks.exchange,g_main.Stocks.TickerName                           |   56 | Using where                                  |
|  1 | SIMPLE      | stock_list     | eq_ref | PRIMARY,stock_list_mktcap,stock_list_price,stockfscore,stockfzcore,symbol,exchange | PRIMARY  | 22      | g_main.triple_insider.symbol,g_main.triple_insider.exchange               |    1 | Using where                                  |
+----+-------------+----------------+--------+------------------------------------------------------------------------------------+----------+---------+---------------------------------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

Possible duplicates: http://stackoverflow.com/questions/3835390/mysql-slow-query-join-multiple-wheres-order-by http://stackoverflow.com/questions/3497850/mysql-slow-query-with-join-even-though-explain-shows-good-plan — showdev, Apr 11 '13 at 20:23
First comment: You cannot test columns from a left-joined table (`stock_list` and `GuruList` in your case) in the WHERE clause. If you do, you force the LEFT JOIN to behave as if it was an INNER JOIN. Instead, those tests should be made part of the JOIN condition. — Joe Stefanelli, Apr 11 '13 at 20:27

score 0 · Accepted Answer · answered Apr 11 '13 at 20:31

Create an index on triple_insider.date.

You might also want to either get rid of LEFT JOIN or move the conditions on everything but triple_insider to the appropriate ON clauses.

Also DISTINCT, though a legit construct by itself, seems to be a wrong way to solve a problem you're not describing in your post.

MySQL Slow on join and order by

1 Answers1

Linked