To answer your question of which is faster....see the benchmark below...there's
no difference at 100,000 records. The index creation is a bit faster for
integer.
But for your data you won't be doing many inserts or updates on items...more on
prices so the grocery_type_index won't get touched very much.
#include <stdio.h>
main()
{
int i;
for(i=0;i<100000;++i) {
char sql[4096];
char *type;
if ((i%5)==0) type="fruit";
if ((i%5)==1) type="vegetable";
if ((i%5)==2) type="meat";
if ((i%5)==3) type="sundrie";
if ((i%5)==4) type="bakery";
sprintf(sql,"insert into store
values(%d,'%s',155);\n",i%5,type,"testing"); // for int grocery type
//sprintf(sql,"insert into store
values('%s','%s',155);\n",type,"testing"); // for string grocery type
printf("%s",sql);
}
}
sqlite> create table store (grocery_type text, description text,price int);
sqlite> begin;
sqlite> .read data1.sql
sqlite> commit;
sqlite> .timer on
sqlite> create index grocery_type_index on store(grocery_type);
CPU Time: user 0.247962 sys 0.005999
sqlite> select count(*) from store where grocery_type='fruit';
20000
CPU Time: user 0.002999 sys 0.000000
sqlite> select count(*) from store where grocery_type='vegetable';
20000
CPU Time: user 0.003000 sys 0.000000
sqlite> select count(*) from store where grocery_type='meat';
20000
CPU Time: user 0.003000 sys 0.000000
sqlite> select count(*) from store where grocery_type='meat';
20000
CPU Time: user 0.003000 sys 0.000000
sqlite> select count(*) from store where grocery_type='sundrie';
20000
CPU Time: user 0.002999 sys 0.000000
Now do it with integer grocery_type
sqlite> create table store (grocery_type int, description text,price int);
sqlite> begin;
sqlite> .read data2.sql
sqlite> commit;
sqlite> .timer on
sqlite> create index grocery_type_index on store(grocery_type);
CPU Time: user 0.197970 sys 0.003000
sqlite> select count(*) from store where grocery_type='vegetable';
0
CPU Time: user 0.000000 sys 0.000000
sqlite> select count(*) from store where grocery_type=0;
20000
CPU Time: user 0.002999 sys 0.000000
sqlite> select count(*) from store where grocery_type=1;
20000
CPU Time: user 0.003000 sys 0.000000
sqlite> select count(*) from store where grocery_type=2;
20000
CPU Time: user 0.002999 sys 0.000000
sqlite> select count(*) from store where grocery_type=3;
20000
CPU Time: user 0.003000 sys 0.000000
sqlite> select count(*) from store where grocery_type=4;
20000
CPU Time: user 0.003000 sys 0.000000
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: [email protected] [[email protected]] on
behalf of Shorty [[email protected]]
Sent: Tuesday, October 11, 2011 8:31 PM
To: [email protected]
Subject: EXT :[sqlite] Speed of sorting - diff between integer and string
I am building a database that is going to be used to produce a list of items
on my website. The list is created depending on how the user wants the
list sorted. For example, lets say I am going to list groceries that are
available in my store. Users will typically select the type of grocery
they want to see, like list all the fruits, or a list all vegatables.
Here is an example table:
COLUMNS:
grocery_type, description, price
ROWS:
fruit, apple, 1.23
fruit, banana, 5.35
vegetable, carrot, 1.55
vegetable, spinach, 6.85
-- HERE IS MY QUESTION: --
Is faster for the sqlite database to have the grocery_type as a string or
integer?
Or is the speed difference so small it doesn't matter?
Like instead of having "fruit" in the grocery_type column, I could have it
as a "1" type, vegetables would be "2", and then translate that just before
spitting out the table to my web page.
Thanks -
Shorty
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
