When I did the index test I did not have the second table in the DB.
Just the complete large table (id, topics, definition). I also didn't have
order by random() limit 1
In the select statment. The data is alread in alphabetical order, but I'll try
that anyway.
I am going to be away from the office over the next week or so but I will look
into this some more as I get time.
Thank you both again for your time and patience.
Tim
On 3/21/2012 2:53 AM, Max Vlasov wrote:
On Wed, Mar 21, 2012 at 1:33 AM, Tim Morton<t...@preservedwords.com> wrote:
So it seems the index is no help;
Are you sure you did as Simon explained?
Becausem my tests shows the trick works... (sqlite 3.7.10)
Create table [TestTable] ([Id] integer primary key, [topics] Text,
[Definition] Text);
CREATE TABLE [SourceTable] ([Id] integer primary key, [Title] Text);
Insert into SourceTable (Title) VALUES ('bar')
Insert into SourceTable (Title) VALUES ('bar')
Insert into SourceTable (Title) VALUES ('foo bar')
Insert into SourceTable (Title) VALUES ('bar foo')
insert into TestTable (topics, definition) values ((Select Title from
SourceTable order by random() limit 1), 'abcdefghij ...') (about 500
symbols)
100,000 times so we have about 100,000 records
select topics, id from TestTable
:Sqlite read 103 Mb (I have a special "hack" into vfs to monitor how much
sqlite read during the query)
:Time: 4,9 sec
...After adding the index
CREATE INDEX [idx_Trick] ON [TestTable] ([Topics], [Id])
select topics, id from TestTable order by topics,id
:Sqlite read 2 Mb
:Time: 1,3 sec
The timing was unaffected by sqlite and system cache since the db was
reopened with a special trick on Windows forcing it to clear cache for this
file
Max
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
