>>>BTW, get rid off the deleted tag. It does not speed up your query (except in very specific circumstances). Full optimization does not mean that it is most optimal.
>>
>>that used to be one of my standard first checks. It changed to making sure if the index was at least binary and then thinking on deleting<g>.
>>On the off chance that the deleted() index helps at least somewhere in the app the slight slurp of the pure bitmap of a binary index helps MUCH more than I had imagined after first readin about it.
>
>I've gone through that before. An index on deleted() is about useless except in very specific case. None of them is particulary usefull (except for COUNT()). The low selectivity does not speed up your queries, esspecially if your tables do not contain or few deleted records.

Usually I am thrown into a situation where I need to fix the worst performing parts of an app and only given a few days budget. In such situations I don't like to delete an index if I can eliminate the pain. I found in situations where the presence/absecence of a regular deleted() index made a difference between 15 and 25 times, the difference between no index and binary index was much smaller (taking less than twice the amount of time needed for the queries without binary). There were other non-count queries in which the binary _del got the fastest results even compared to no-_del-index (repeatable across diferent data sets, measured across different machines and with high resolution, so I don't think this was something totally artificial), but the summed query time of "no index" solution was indeed the fastest in my measurements.

But since my measured coverage was less than 10% of the total app and the change from regular to deleted with a few other tricks eliminates most of the thumb twiddling, it was "good enough" - and there were also have a few count() as well, which were not inside the "fine measuring" as the code parts they were in was not among the "slow" ones. Yes, given a larger budget for testing the app would probably be snappier even without binary _del, but the main thumb twiddling was resolved within the budget. Not a perfect world<g>.

>I'm sure what you've seen, but under most conditions, it is technically impossible that even a binary index would speed up anything beyond a few milliseconds.

I am running apps where such queries are done a couple of hundered thousand times: the difference of a few ms can hurt.

regards

thomas