Expain the result of "explain" query in mysql

Question

I am using indexing for mysql tables.

My query was like this

 EXPLAIN SELECT * FROM `logs` WHERE userId =288 AND dateTime BETWEEN '2010-08-01' AND '2010-08-27' 

I have indexing on field userId for this table logs, and the result of explain query is like below.

id  select_type     table     type  possible_keys   key     key_len     ref     rows    Extra
1      SIMPLE         logs     ref      userId      userId      4       const   49560   Using where

The question is "My indexing is really useful or not?"...

Thanks in advance

@fastmultiplication

I thought that indexing on both this field might increase load on mysql as there will be lot of entries with unique (userId and dateTime). I have tried adding indexing on both userId_dateTime and the result is

id  select_type     table   type    possible_keys            key        key_len     ref     rows    Extra
1      SIMPLE        logs    ref     userId_dateTime    userId_dateTime     4       const   63455   Using where

Answer 1

Your query is using indexes, and yes, they are useful. You might find the following doc pages useful:

EXPLAIN Output Format
How MySQL Uses Indexes
Multiple-Column Indexes

Also:

Multiple column index vs multiple indexes

MySQL will usually use the index that returns the smallest number of rows. In your first example, MySQL is using the userId index to narrow down the number of rows to 49560. That means that userId does not contain unique values (if it did, you wouldn't need the date range condition). As there is no index on the dateTime column, it then has to scan each row to find the ones that meet your date range criteria.

In your second example, you appear to have created a compound (multiple-column) index on userId and dateTime. In this case, it appears as though MySQL is not able to use the latter half of the index for the BETWEEN clause—I'm not sure why. It may be worth trying it with two separate indexes, rather than a multiple-column index. You may also want to try replacing BETWEEN with:

'2010-08-01' >= AND <= '2010-08-27'

This should be identical, but see the following bug report, which may affect your version of MySQL:

Optimizer does not use index for BETWEEN in a JOIN condition

Answer 2

From the 'rows' field it looks like MySQL still estimates it will have to look at a lot of rows.

You should try adding an index to the dateTime field, too.

And for this particular query, maybe an index on both of the fields.

alter table logs add index user_datetime (userId,dateTime);

Answer 3

How many rows should the query return? And how fast is the query running?

It looks to me like a pretty simple query, which is using the correct index, so if it is slow for some reason, it is probably because it has to actually return a lot of data. If you are not actually interested in all the rows, you can use LIMIT to get less.