A simple MySQL query forever (over 20 minutes!)

Question

Maybe you can help me. I need to query 3 tables to get data for financial stock.

The idea is to go to the tool table, find the index for each tool, and then bring all the prices for this particular tool together with indicators that are on a separate table.

The stockdata and indicators tables are almost 50,000 records. instruments total 30.

This is a request that does not work:

 SELECT indicators.ddate, instruments.name, indicators.sma_14, indicators.sma_5, stockdata.close FROM indicators INNER JOIN instruments ON (indicators.instrument_id=instruments.id) INNER JOIN stockdata ON (instruments.name=stockdata.name) 

Here is the result of EXPLAIN

 +----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | rows | Extra | +----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+ | 1 | SIMPLE | instruments | index | PRIMARY,instruments_index01 | instruments_index01 | 61 | 25 | Using index | | 1 | SIMPLE | indicators | ref | indicators_index01 | indicators_index01 | 5 | 973 | Using where | | 1 | SIMPLE | stockdata | ref | stockdata_index01 | stockdata_index01 | 31 | 1499 | Using where | +----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+ 

I really appreciate any help you can provide!

This is the schema for the parts of the tables that are involved in my question:

 TABLE `indicators` ( `id` int AUTO_INCREMENT NOT NULL,<br> `instrument_id` int, `date` date, `sma_5` float(10,3), `sma_14` float(10,3), `ema_14` float(10,3), /* Keys */ PRIMARY KEY (`id`) ) TABLE `instruments` ( `id` int AUTO_INCREMENT NOT NULL, `name` char(20), `country` char(50), `newsquery` char(100), /* Keys */ PRIMARY KEY (`id`) ) TABLE `stockdata` ( `id` int AUTO_INCREMENT NOT NULL, `name` char(10), `date` date, `open` float, `high` float, `low` float, `close` float, `volume` int, `adjclose` float, /* Keys */ PRIMARY KEY (`id`) ) 

Answer 1

You join the indicators table to the instruments table, and the indicators.instrument_id column is not indexed.

You also join the instruments table to the stockdata table using the instruments.name and stockdata.name , both of which are of type CHAR . Joining using CHAR or VARCHAR usually significantly slower than joining using INT columns:

Using CHAR keys for connections, how much overhead?

To make matters worse, your CHAR columns have different sizes ( char(20) and char(10) respectively) and they are not indexed. It really complicates the work of MySQL! See How MySQL Uses Indexes for more information.

Ideally, you should change your table structure so that the join can be made using indexed INT fields. Something like that:

 CREATE TABLE `instruments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `country` char(50) DEFAULT NULL, `newsquery` char(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `indicators` ( `id` int(11) NOT NULL AUTO_INCREMENT, `instrument_id` int(11) DEFAULT NULL, `date` date DEFAULT NULL, `sma_5` float(10,3) DEFAULT NULL, `sma_14` float(10,3) DEFAULT NULL, `ema_14` float(10,3) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_instrument_indicators` (`instrument_id`), CONSTRAINT `fk_instrument_indicators` FOREIGN KEY (`instrument_id`) REFERENCES `instruments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; CREATE TABLE `stockdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `instrument_id` int(11) NOT NULL, `name` char(20) DEFAULT NULL, `date` date DEFAULT NULL, `open` float DEFAULT NULL, `high` float DEFAULT NULL, `low` float DEFAULT NULL, `close` float DEFAULT NULL, `volume` int(11) DEFAULT NULL, `adjclose` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_instrument_stockdata` (`instrument_id`), CONSTRAINT `fk_instrument_stockdata` FOREIGN KEY (`instrument_id`) REFERENCES `instruments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; 

Then use the indexed fields in your connection:

 SELECT indicators.date, instruments.name, indicators.sma_14, indicators.sma_5, stockdata.close FROM indicators INNER JOIN instruments ON (indicators.instrument_id=instruments.id) INNER JOIN stockdata ON (instruments.id=stockdata.instrument_id) 

Using INT indexed columns, your joins will be much faster. Using InnoDB constraints can help ensure data integrity.

If there is a reason you should join the name column, make both sizes and index them.

Answer 2

 SELECT ind.ddate, ins.name, ind.sma_14, ind.sma_5, sto.close FROM indicators ind JOIN instruments ins ON ind.instrument_id = ins.instrument_id JOIN stockdata sto ON ins.name = sto.name 

another variant:

 select ind.ddate, ins.name, ind.sma_14, ind.sma_5, (select close from stockdata where name = ins.name limit 1) as close from indicators ind join instruments ins on ind.instrument_id = ins.instrument_id 

Answer 3

I am suspicious of the connection in the stockdata.name field. Do you have the correct indexes defined in the name field in the stock and instrument table? Is it possible that joining a name may return incorrect results, and you could join another .id field?

Answer 4

You are requesting the name of an unindexed field in sharedata. Either create an index, or join id instead. (I would do the latter by changing the name to id in the tools)