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.

migrated from stackoverflow.com Oct 6 '15 at 21:32

This question came from our site for professional and enthusiast programmers.

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

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

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

Your Answer

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