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

2011/2/11 Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>:
>> 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.

Concurrently? Are there any ways to add large indexes without
blocking inserts to the table for the time it takes to create the
index?

> 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

I actually considered that. For the test case given it works very
fast. Not sure if it would work universally ... it scales well when
having extreme amounts of transactions outside the given transaction
list (the case we have problems with now), but it wouldn't scale if
some user has an extreme amount of transactions within the list.
However, I think our "extreme amount of transactions"-problem is
mostly limited to the transaction types outside the list.