Hugh Gibson wrote: >> I'm intrigued. How do you get SQLite to use a multi-column index as it's >> primary key (i.e. B-tree hash)? Please elaborate. > > Simply > > CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT > '',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY > (sCommunityID, sTransactionID))
Ah, but it's not used for the B-tree hash (at least not according to the
documentation).
>> Have you investigated the following to see how the optimizer deals with
>> it?
> ...snip...
> I'm not fluent in the VDBE code, but ISTM (and execution time confirms)
> that it's doing a table scan first to do the GROUP BY. Then it
> creates a temp table with the values from the list, and does a join (I
> suppose).
I thought it probably would, but worth trying :)
>> I think I'd normally err on keeping a filter table handy (temporary if
>> you wish):
>>
>> -- Initialise filter:
>> CREATE TABLE filter (sCommunityID TEXT);
>> CREATE INDEX filter_idx ON filter(sCommunityID);
>>
>> -- Configure filter:
>> INSERT INTO filter VALUES ('a030600001bFi');
>> INSERT INTO filter VALUES ('a030600004KDy');
>> INSERT INTO filter VALUES ('a030600008QhK');
>>
>> -- Get your results:
>> SELECT sCommunityID, max(sTransactionID)
>> FROM filter NATURAL INNER JOIN TransactionList
>> GROUP BY sCommunityID;
>>
>> -- Clear filter ready for next time:
>> DELETE FROM filter;
>
> Thread safety is an issue here. Multiple threads may be doing this action.
> Also, by the (admittedly contorted) query I am using I can get the VDBE to
> create a temporary table for me and insert the data, taking a lot less
> time to do it than it would take to run all those queries.
You didn't mention threads :)
Robin
--
Robin Breathe, Computer Services, Oxford Brookes University, Oxford, UK
[EMAIL PROTECTED] Tel: +44 1865 483685 Fax: +44 1865 483073
signature.asc
Description: OpenPGP digital signature
