InnoDB can use multiple indices on SELECT

Back in the old days, MySQL could only use a single index for a basic SELECT (each join gets its own index). So a query like this would cause trouble:

SELECT * FROM `users` WHERE (city_location_id IN (...) OR neighborhood_location_id IN (...))

The second where condition will cause a full table scan even though there is an separate index for both city_location_id and neighborhood_location_id.

However, if you're using InnoDB tables, MySQL can optimize for this buy creating a index UNION for you. Read more about it here if interested.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-intersection.html