Dail
Dail

Reputation: 4606

How can I optimize this query?

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

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

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

KM.
KM.

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

Aerik
Aerik

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

Related Questions