Mysql – Very Simple MySQL Inner Join Extremely Slow

MySQL

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?

UPDATE

I was on MySQL 5.5, I have updated to MariaDB 10.1.7 to give that a shot. No change. The fields in both tables are defined as varchar(9) utf8_general_ci NULLABLE. The indexes are BTREE, not unique, not packed, NULL yes, Collation A. I moved the two tables in question to ARIA storage engine just to give that a shot as well, no change.

I also noticed mysqld is using almost no memory (thanks @Uueerdo). I am wondering if this has something to do with it. I am not sure what settings I need to change exactly.

Best Answer

I ended up moving the tables back to InnoDB and then increasing the innodb_buffer_pool_size to 8G. It's still not sub 1 second like our MS SQL Server, but it is finishing in under 5 seconds, so a huge step in the right direction.