Re: query not using index

[Jason Collison]

Maybe it would help if you posted your CREATE TABLE statement along with the indexes. Nevertheless... your "real" query cannot work as expected, for starters, because of the LENGTH function that's inserted right there in the middle of the two indexed columns. You'll need to move that to before or after the customer_id clause. But that still doesn't explain why the "stripped down" version isn't working.

Let's assume that we're only dealing with your stripped-down query. and we'll assume that 'calldate' is indexed as you said it was. Quickly two simple things:

- Is there data in that table that does _not_ match your stripped down criteria? (Old, or impossibly new call_dates) If not, I believe mysql will do a full table scan because it knows the index will do no good anyway. And of course one should run ANALYZE TABLE after adding such "dummy" data.

- Have you tried removing the sort or changing its direction?


But assuming those have been tried, what jumps right out at me is where you're blindly comparing a (date?) column to a string. (I can only assume it was declared as a date coluimn.) But in the query, no typecast, no nothing. Does this really work properly in mysql?? God I'd hope not. I'm not "up" on mysql DATEs because I prefer to roll-my-own depending on the requirements, but at first glace this is what I see. I'd try this on a duplicate table using a purely numeric time_t instead and see if you get the same problem. I'll bet you won't. Then I'd use the proper mysql inline type conversion functions and see if I can get the same proper behaviour.

Not _quite_ a shot in the dark, but this board is slow as molasses so I figured I'd reply. But I can say that I've never once had this problem, and I don''t rely upon automatic type conversions. I do not think this is a coincidence.