Reputation: 4606
I have two mysql tables with the follow structure:
mysql> describe symbol;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| symbol | varchar(250) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe price;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| id_symbol | int(11) | NO | MUL | NULL | |
| date | date | NO | MUL | NULL | |
| price | double | NO | | NULL | |
+-----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
I have to query the price table to get a list with N prices of two symbols (to compare them)
This is a sample result:
DATE SYMBOL 1 SYMBOL 2
2011-01-01 100 23
2011-01-02 92 26
2011-01-03 89 50
So I need to find the same data of both and return their prices. I have created a query that seem it does the job, but maybe is not very optimized.
SELECT *
FROM price AS a
JOIN price AS b ON a.date = b.date
WHERE a.id_symbol = 1 AND b.id_symbol = 2
ORDER BY a.date DESC
LIMIT 100
What do you think?
Can I optimize the query doing something (or maybe change something on the tables structure)?
EDIT:
This is the result of the EXPLAIN {query}
mysql> EXPLAIN SELECT *
-> FROM price AS a
-> JOIN price AS b ON a.date = b.date
-> WHERE a.id_symbol = 1 AND b.id_symbol = 2
-> ORDER BY a.date DESC
-> LIMIT 100;
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | a | ref | date,id_symbol | id_symbol | 4 | const | 921 | Using where; Using filesort |
| 1 | SIMPLE | b | ref | date,id_symbol | id_symbol | 4 | const | 966 | Using where |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
2 rows in set (0.03 sec)
What do you think?
Then... what is 921 and 966 on the EXPLAIN "ROW" column ? If i count the prices i have for symbol 1 and symbol 2 i get: 912 and 912 :-| what is that?
(P.S. Yes, my mistake i have to change * with the columns i want)
Upvotes: 0
Views: 131
Reputation: 44333
Here is your original query
SELECT *
FROM price AS a
JOIN price AS b ON a.date = b.date
WHERE a.id_symbol = 1 AND b.id_symbol = 2
ORDER BY a.date DESC
LIMIT 100;
You need to make two major changes:
CHANGE #1 : REFACTOR YOUR QUERY
Using only keys, make WHEREs, ORDER BYs happen first, JOINs last
SELECT A.date,GROUP_CONCAT(A.price ORDER BY A.id_symbol) prices
FROM price A
INNER JOIN
(
SELECT BB.* FROM
(
SELECT DISTINCT date FROM
(
SELECT AAAA.date FROM
(SELECT date FROM price WHERE id_symbol = 1) AAAA
INNER JOIN
(SELECT date FROM price WHERE id_symbol = 2) BBBB
USING (date)
ORDER BY AAAA.date
) AAA ORDER BY date DESC LIMIT 100
) AA
INNER JOIN
(SELECT date,id_symbol FROM price WHERE id_symbol in (1,2)) BB
USING (date)
ORDER BY BB.date DESC,BB.id_symbol
) B
USING (date,id_symbol) GROUP BY date;
CHANGE #2 : CREATE INDEX TO SUPPORT THE REFACTORED QUERY
ALTER TABLE price ADD INDEX symbol_date_id_ndx (id_symbol,date,id);
ALTER TABLE price ADD INDEX date_id_ndx (date,symbol_id);
Give it a Try !!!
Upvotes: 1
Reputation: 103579
based on the limited info, about all you can do is create an index on id_symbol ASC, date DESC
and possibly limit the *
to only the columns you need.
Your biggest "speed up" may be to combine your two queries into a single one and then just run that one query and parse the result set instead of running one query to get all the symbol pairs and then a different query for each symbol pair. You don't provide the original query, so it is impossible to suggest this combined query.
Upvotes: 1
Reputation: 2317
Setting aside the perhaps very appropriate comments about premature optimization, have you tried using EXPLAIN (http://dev.mysql.com/doc/refman/5.1/en/using-explain.html) to see how your query is actually executed? From there you may want to look into adding indexes.
Upvotes: 0