Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
Please provide with SHOW CREATE TABLE output for both tables. While at it, the output of EXPLAIN would be useful too. –  Shlomi Noach Oct 18 '13 at 12:12
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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