On Tue, Jul 7, 2009 at 10:20 AM, Michal Seliga<michal.sel...@visicom.sk> wrote:
> Robert Citek wrote:
>> create table foo (
>> col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9,
>> col_10, col_11, col_12, col_13, col_14, col_15, col_16, col_17,
>> col_18, col_19, col_20, col_21, col_22 ) ;
>> .mode tab
>> .imp "foo.tsv" "foo"
>> select col_9, col_22, count(*) as "count" from foo group by col_9, col_22 ;
>
> Try this with index created on foo(col_9, col_22).
Significantly worse than not creating an index:
$ time -p {
grep -v '^#' <<eof
drop table if exists foo ;
create table foo (
col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9,
col_10, col_11, col_12, col_13, col_14, col_15, col_16, col_17,
col_18, col_19, col_20, col_21, col_22 ) ;
.mode tab
.imp "foo.tsv" "foo"
create index ifoo on foo (col_9, col_22) ;
select col_9, col_22, count(*) as "count" from foo group by col_9, col_22 ;
eof
} | TMPDIR=/dev/shm/ sqlite3 foo.sqlite | wc -l
^Creal 1626.73
user 74.31
sys 16.68
I stopped it after almost 30 minutes.
Regards,
- Robert
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
