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.

1,000,000 rows

5,724,842 rows

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

Written By
Slow Join Performance
October 06, 2015 08:45AM
October 06, 2015 02:38PM
October 06, 2015 03:42PM
November 14, 2015 10:24AM
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.