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:
"Sending data" is a single huge bottleneck. Is this about a connection between my client & server more than query perf time?
show create table Test_schema.Events ;
userID
is either not integer (but some wide char, eg.varchar(45)
), or that the index does not have theuserID
column as the first column. Or both.userID
is indeedvarchar(45)
! I'll retry w/ an unsigned