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

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 2

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.