0

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.

  • Just a guess, but maybe the server doesn't have enough memory, so it can't keep both indexes in RAM. – Barmar Oct 6 '15 at 16:35
  • I have 16GB of memory and 8 XEON 3.47 cores. I think 16GB should be enough for this test. – Dereck Oct 6 '15 at 16:47
  • Hopefully this question will get migrated to dba.SE and people knowledgeable will be able to help you. The query can't be made any simpler, obviously. – Barmar Oct 6 '15 at 16:48
  • @Dereck The system could have terabytes of memory, but if the MySQL server has not been configured to use it, it won't. Also, post CREATEs; how the string fields are defined and indexed could affect your performance. – Uueerdo Oct 6 '15 at 16:56
  • @Dereck Please refer: percona.com/blog/2006/12/01/count-for-innodb-tables – fortune Oct 6 '15 at 17:38
2

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.

| improve this answer | |
  • You might try char(9) instead of varchar to get a little boost by not checking the lengths etc, but I my guess would be that you do not get more than 5% difference from that. – jkavalik Oct 7 '15 at 7:36
  • Or you might encode the string to bigint somehow (if it is only printable characters then it should be possible) to see if the problem comes from string comparisons. – jkavalik Oct 7 '15 at 7:40
0

I would try rebuilding the indexes.

| improve this answer | |
  • I have rebuilt the indexes and ran OPTIMIZE TABLE on the tables. The Tables and Indexes were just built and populated yesterday. – Dereck Oct 6 '15 at 16:47
0

(Not an answer, but to undo some wrong statements.)

  • Changing to CHAR(9) would force all strings to be 27 bytes, and probably increase the INDEX size, thereby making it harder to keep in RAM.
  • MySQL does not use more than 1 CPU per query.
  • Various versions and Engines won't make much difference -- It is a full scan of the 1M rows in A, then lookups into B. Note that both are "Using index".
  • BIGINT instead of VARCHAR probably won't make much difference.

Maybe the real question is "why do you need that particular query"?

Can you run the query once a day and remember the answer?

Or other "optimizations" that 'accept' that the query is slow.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy