Re: Why we don't want hints Was: Slow count(*) again...

11.02.11 11:29, Tobias Brox написав(ла):
> 2011/2/11 Віталій Тимчишин<tivv00@gmail.com>:
>> If the list is hard-coded, you can create partial index  on
>> account_transaction(account_id, created desc) where trans_type_id in ( ...
>> long, hard-coded list ...)
> My idea as well, though it looks ugly and it would be a maintenance
> head-ache (upgrading the index as new transaction types are added
> would mean "costly" write locks on the table,
Create new one concurrently.
>   and we can't rely on
> manual processes to get it right ... we might need to set up scripts
> to either upgrade the index or alert us if the index needs upgrading).
Yep. Another option could be to add query rewrite as

select  * from (
select * from account_transaction where trans_type_id =type1 and 
account_id=? order by created desc limit 25 union all
select * from account_transaction where trans_type_id =type2 and 
account_id=? order by created desc limit 25 union all
...
union all
select * from account_transaction where trans_type_id =typeN and 
account_id=? order by created desc limit 25
) a
order by created desc limit 25

This will allow to use three-column index in the way it can be used for 
such query. Yet if N is large query will look ugly. And I am not sure if 
optimizer is smart enough for not to fetch 25*N rows.


Best regards, Vitalii Tymchyshyn