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
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?
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.