Optimizing a slow query to better use indexing I have a query that looks like the following:

SELECT time_stop, some_count
        FROM foo
        WHERE user_id = 1
        AND time_start &gt;= '2016-07-27 00:00:00'
        AND time_stop &lt;= '2016-07-27 23:59:59' 
        AND some_count = ( SELECT MAX(some_count) 
          FROM foo
          WHERE user_id = 1
          AND tm_start &gt;= '2016-07-27 00:00:00'
          AND tm_stop &lt;= '2016-07-27 23:59:59'
       );


The table schema looks something like this:

Create Table: CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `time_start` datetime(6) DEFAULT NULL,
  `time_stop` datetime(6) DEFAULT NULL,
  `some_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),,
  KEY `user_id` (`user_id`),
  KEY `time_start` (`time_start`)
) ENGINE=InnoDB AUTO_INCREMENT=418005923 DEFAULT CHARSET=latin1


EXPLAIN output looks like the following:

+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys      | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |     1.67 | Using where |
|  2 | SUBQUERY    | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |    16.66 | Using where |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+


I'm using MySQL 5.7.11.

I think the major problem I'm having is there's very little cardinality in the indexed keys here. The query is attempting to get the largest value of some_count for a given date range. It also wants the exact datetime that the max value was met. This could return multiple rows, which returns to the little cardinality problem.

I'm not sure if I could re-write the query to better work with the existing indexes, I'm guessing I could though.
Your query seems to be simply this:

SELECT time_stop, some_count
    FROM foo
    WHERE user_id = 1
    AND time_start &gt;= '2016-07-27 00:00:00'
    AND time_stop &lt;= '2016-07-27 23:59:59'
 ORDER BY some_count DESC LIMIT 1;


You need an index on (user_id,time_start) -- in that order -- and, assuming start is always earlier than stop, adding the seemingly-unnecessary AND time_start &lt;= '2016-07-27 23:59:59' will allow the database to avoid even considering rows with time_start values later.  

Also adding time_stop to the same index will not help you, for the same reason that the telephone directory won't help you find all people with first names beginning with the letter L.  Adding some_count to the index also won't help, for the same reason.  In a multi-column index, the values in column B are sorted within identical groups of column A (hence the telephone directory reference). The values in column C are sorted within identical groups of B, etc... so if A is an equality comparison, and B is a range, as in your query, those are easy to pinpoint... but the values of C appear random.  For C to be usable in an index, B needs also to be an equality comparison, which it is not, in your case.

However, if these are the only columns you're reading, then adding those columns to the index (to the right of user_id and time_start) could be useful for a different reason -- when a query uses an index that contains all the columns selected, it's a "covering index," and the entire result can be read directly from the index, instead of the main table, potentially giving a benefit that you have to weigh against the cost of maintaining larger indexes.