MySQL Performance – Should Better Performance Be Expected?

MySQL

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?

Best Answer

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.

Related Question