I have a mySQL table with about 30M rows, with an int(10) primary key id index (auto-increment) and a few other id columns eg. modem_id. modem_id column has a BTREE index with cardinality of about 300.
I am doing a very simple query eg.
SELECT MAX(`id`) FROM `messages` WHERE `modem_id`=1234
I would think this should execute very quickly but it is consistently taking 1-2 seconds. Profiling the query in phpMyAdmin tells me that the time is all in the "Sending Data" state which doesn't make sense because the only data returned is one integer value.
How can this be sped up?
ADD INDEX(modem_id, id)
In general, the query
SELECT MAX(x) FROM t WHERE y = 1234
Needs INDEX(y, x) -- in that order.
If you have INDEX(y) and the table is ENGINE=InnoDB and x is the PRIMARY KEY, then INDEX(y, x) is the same as INDEX(y). And your two SELECTs will probably run in identical time and have the same EXPLAINs.
Without seeing SHOW CREATE TABLE, I can't tell if you have another problem -- When doing y = 1234 and y is VARCHAR, the test cannot use an index. Either change y to INT or quote the constant: y = "1234".
The speedup of 100+ that you got may have been due to caching. Run both queries twice. For further analysis, provide SHOW CREATE TABLE and EXPLAIN FORMAT=JSON SELECT ... of both queries.