MySQL: Help comparing two tables

October 7, 2010 at 18:00:12
Specs: centOS 5, 3Ghz/2GB
Hi,

I have a simple database with two tables, each containing ~170k rows in it. Each row contains a numeric ID (prim key), a symbol and a numerical value with differing decimal precision.

mysql> desc srlabs;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| symbol | varchar(40)   | YES  |     | NULL    |                |
| settle | decimal(12,8) | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc wombat;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| symbol | varchar(40)   | YES  |     | NULL    |                |
| settle | decimal(12,8) | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

The "srlabs" table has about 170,000 rows and the "wombat" table has about 170,700. Both tables likely have about 150K to 160K symbols in common and I'm trying to find which of those symbols have different values in the "settle" field. I've attempted to do so with either of the following statements, but both take hours and I ended up killing the query. I ran both successfully with a much smaller subset of matching data (25 rows, etc) and both ran in under a half-second.

Am I doing something wrong and is there a better way of doing it? I can rebuild each table if necessary(I've done so several times already, with different types, etc.)

select * from cfWombat a JOIN cfSrlabs b ON a.symbol = b.symbol AND a.settle != b.settle;

select * from cfWombat a JOIN cfSrlabs b ON a.symbol = b.symbol AND a.settle != b.settle;

thanks in advance


See More: MySQL: Help comparing two tables

Report •


#1
October 7, 2010 at 18:03:35
Sorry, here is the second of the two queries I attempted to run:

select * from cfWombat wbt, cfSrlabs srl where wbt.symbol = srl.symbol and wbt.settle != srl.settle;


Report •

#2
October 8, 2010 at 18:57:44
Is symbol unique?

Try adding an INDEX on the symbol column, and (if applicable) make it UNIQUE.

Also - which database engine are you using? (MyISAM is much faster than InnoDB when doing a lot of JOIN's)


Report •

#3
October 9, 2010 at 18:19:03
that was it! I added an index to the symbol columns in each table and the 3hr query took 3.68 seconds to run. It's probably obvious that I'm fairly new to mySQL.

I issued the following command on both tables:

create index symbol_index on cfWombat(symbol);
create index symbol_index on cfSrlabs(symbol);

thanks again.


Report •

Related Solutions


Ask Question