CODEWITHSUNDEEP
mysqlperformancejoin
user1094786
user1094786

Reputation: 6800

MySQL join issue, query hangs

I have a table holding numeric data points with timestamps, like so:

CREATE TABLE `value_table1` (
  `datetime` datetime NOT NULL,
  `value` decimal(14,8) DEFAULT NULL,
  KEY `datetime` (`datetime`)
) ENGINE=InnoDB;

My table holds a data point for every 5 seconds, so timestamps in the table will be, e.g.:

"2013-01-01 10:23:35"
"2013-01-01 10:23:40"
"2013-01-01 10:23:45"
"2013-01-01 10:23:50"

I have a few such value tables, and it is sometimes necessary to look at the ratio between two value series. I therefore attempted a join, but it seems to not work:

SELECT value_table1.datetime, value_table1.value / value_table2.rate 
    FROM value_table1
    JOIN value_table2
    ON value_table1.datetime = value_table2.datetime
    ORDER BY value_table1.datetime ASC;

Running EXPLAIN on the query shows:

+----+-------------+--------------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | value_table1 | ALL  | NULL          | NULL | NULL    | NULL | 83784 | Using temporary; Using filesort |
|  1 | SIMPLE      | value_table2 | ALL  | NULL          | NULL | NULL    | NULL | 83735 |                                 |
+----+-------------+--------------+------+---------------+------+---------+------+-------+---------------------------------+

Edit Problem solved, no idea where my index disappeared to. EXPLAIN showed it, thanks!

Thanks!

Upvotes: 0

Views: 475

Answers (1)

datasage
datasage

Reputation: 19563

As your explain shows, the query is not using indexes on the join. Without indexes, it has to scan every row in both tables to process the join.

First of all, make sure the columns used in the join are both indexed.

If they are, then it might be the column type that is causing issues. You could create an integer representation of the time, and then use that to join the two tables.

Upvotes: 2

Related Questions