The query:

SELECT *, u.user_id 
FROM xgate_geoloc g 
  JOIN tig_users u 
  ON g.uid = u.uid                                    
         -- 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) 
  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!

  • 2
    Please provide with SHOW CREATE TABLE output for both tables. While at it, the output of EXPLAIN would be useful too. Oct 18, 2013 at 12:12

2 Answers 2


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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.