MySQL Forums
Forum List  »  Performance

Slow Join Performance
Posted by: Dereck E
Date: October 06, 2015 08:45AM

I have setup our first MySQL instance and am testing some simple queries. I am attempting to join two tables based on a 9 character string. The field is indexed on both tables and yet the query is taking longer than 10 minutes. When I run the same query on our MSSQL server it runs in under 1 second. I must be doing something wrong.

table_a
1,000,000 rows

table_b
5,724,842 rows

query:
select count(*) from table_a inner join table_b on table_a.field = table_b.field


When I run EXPLAIN I see:
select_type | table | type | possible_keys | key | key_len | ref | rows| extra
SIMPLE | table_a | index | ix_name | ix_name | 30 | NULL | 962446 | Using index
SIMPLE | table_b | ref | ix_name | ix_name | 30 | table_a.field | 1 | Using where; Using Index


When the query is running the server pegs 1 CPU Core at 100% (the other 7 remain idle). The tables are using InnoDB and I do have InnoDB set to 1 file per table.

Any ideas what the heck I am doing wrong?

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Join Performance
1304
October 06, 2015 08:45AM
783
October 06, 2015 02:38PM
762
October 06, 2015 03:42PM
789
November 14, 2015 10:24AM
572
November 26, 2015 02:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.