1

I'm new to MySQL and don't know what performance to expect. Does the performance I'm seeing below seem outrageous, or about right?

I have a table with 4M rows, indexed on userId, and I want to count all of the rows with a certain userId, so I run:

SELECT count(userId) FROM Test_schema.Events where userId = 7205

It takes 0.95 seconds to run this query!

To make sure it's using an index I tried explain:

> explain SELECT count(userId) FROM Test_schema.Events where userId = 7205
# id, select_type, table,  type,  possible_keys,     key,               key_len, ref, rows,    Extra
   1, SIMPLE,      Events, index, idx_Events_userId, idx_Events_userId, 138,        , 3972452, Using where; Using index

I believe this shows that the query will indeed use my index on userId. My server has 13Gb of memory, with 8Gb free.

Should it take MySQL a full second to count 54 rows out of 4 million? Is this about the performance I can expect from MySQL, or should I be hiring an expert to get this down? My target would be well under 100ms, which is about what I can expect from distributed data stores for a similar problem.

--- update ---

I used MySQL Workbench to profile the query, and saw:

profiling

"Sending data" is a single huge bottleneck. Is this about a connection between my client & server more than query perf time?

3
  • Show us the output of show create table Test_schema.Events ; Feb 16, 2016 at 17:20
  • 1
    My (wild) guess is that userID is either not integer (but some wide char, eg. varchar(45)), or that the index does not have the userID column as the first column. Or both. Feb 16, 2016 at 17:24
  • yep, userID is indeed varchar(45)! I'll retry w/ an unsigned
    – Riley Lark
    Feb 16, 2016 at 18:17

2 Answers 2

2

I think "sending data" includes disk access also so probably the whole table is being read for some reason. I would test on the same machine if you're not already to remove the possibility of the network adding latency. You should make the id your primary key as well as adding an index (or make sure to add a unique index). I would be very suspicious of key_len since 138 bytes is very big. You should be using eg, a 64 bit integer and then it would be 8 bytes.

Can you post (at least some of) DESCRIBE Test_schema.Events;?

But to answer the original question, yes, you should be able to get much better performance.

5
  • 1
    In the end I just switched my column from varchar(45) to char(45) and query time went down to 20ms!
    – Riley Lark
    Feb 16, 2016 at 18:49
  • 2
    @RileyLark still wrong - userId = '7205' to query (var)char or change the column to numeric type if it is supposed to represent number.
    – jkavalik
    Feb 16, 2016 at 19:53
  • Not DESCRIBE, SHOW CREATE TABLE !
    – Rick James
    Feb 17, 2016 at 6:43
  • 1
    CHAR will hurt, not help. Your 20ms is probably due to the caching.
    – Rick James
    Feb 17, 2016 at 6:44
  • in my case char helped. Even the first query after a restart of the host machine is 20ms!
    – Riley Lark
    Feb 17, 2016 at 16:15
3

You need an index beginning with UserId. It sounds like you have INDEX(Events, UserId). That was somewhat helpful, but it still had to scan the entire index.

More on building optimal indexes.

3
  • Events is the table name - do I somehow need to make an index on the column w/o including the table?
    – Riley Lark
    Feb 17, 2016 at 16:14
  • 1
    The index name confused me. You apparently have UserId being a VARCHAR or CHAR, INDEX(UserId), but are comparing the string to a number. Quoting the number is the solution.
    – Rick James
    Feb 17, 2016 at 17:35
  • Ah interesting!
    – Riley Lark
    Feb 17, 2016 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.