Should I expect better performance out of 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:

&gt; 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:



"Sending data" is a single huge bottleneck. Is this about a connection between my client &amp; server more than query perf time?
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.