same field in bothe "where" and "order by" - how to index for this query?

Question

The query:

EXPLAIN  EXTENDED 
SELECT *, u.user_id 
FROM xgate_geoloc g 
  JOIN tig_users u 
  ON g.uid = u.uid                                    
WHERE 
         -- corners selection:
          ( ( (lat BETWEEN COALESCE(?, lat) and COALESCE(?, lat)) 
          AND (lat NOT BETWEEN COALESCE(?, lat) and COALESCE(?, lat))
            )
         OR ( (lon BETWEEN COALESCE(?, lon) and COALESCE(?, lon)) 
          AND (lon NOT BETWEEN COALESCE(?, lon) and COALESCE(?, lon))
            )
          )
     AND sex = COALESCE(?, sex)
     AND language = COALESCE(?, language)
     AND city = COALESCE(?, city) 
     AND province = COALESCE(?, province) 
     AND country = COALESCE(?, country) 
     AND time BETWEEN COALESCE(?, time) and COALESCE(?, time) 
ORDER BY time DESC 

1. does that "OR" in the where expression have any effect on te index I should be using ?
2. 'time' field is used in both where and order by. Any way to forge an index in this scenario ?
3. order by time desc - descending order; yet another bummer

Thanks for the input!

Answer 1

I think the problem stopping an index on time being used for filtering is the use of a function with a reference to the row: COALESCE(?, time).

I suspect that mysql's query planner isn't being bright enough to simplify time BETWEEN COALESCE(?, time) and COALESCE(?, time) to time BETWEEN <?1> AND <?1> when both relevant parameters are not NULL and when one is NULL to simiplify COALESCE(?, time) to time when the appropriate param is NULL and then further simplify time BETWEEN time AND <something> to time < <something> or time BETWEEN <something> AND time to time < <something>

Try having separate prepared queries for each case (both times know, the start NULL, the end NULL, and both NULL) instead of trying to do it all in one to give the query planner an easier time over that part.

Answer 2

The OR will most likely affect the index usage, because it hinders index seeking. Generally speaking "OR" is 'bad' for index usage in where clauses.

The time field, it would be sensible to have it in an index sorted in the DESC order when you use it DESC;

Other than that - we'll need more information to give more exact/concrete advice