Though other posters have already pointed out that LIKE can't use an index, I think that it's also worth mentioning that in all three forms of the table you've created, there's an index on b - specifying the UNIQUE keywords silently creates an index for you on that column, to enforce uniqueness. That's why your equality tests all ran in the same amount of time - there was always at least one index present.
-Tom > -----Original Message----- > From: Puneet Kishor [mailto:[EMAIL PROTECTED] > Sent: Saturday, June 11, 2005 10:35 AM > To: [email protected] > Subject: [sqlite] How to improve performance of SELECT... LIKE... > > I started with three identical tables, first with no index, > second with > regular index, third with unique index, about 2,35,000 rows. > > CREATE TABLE t1 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b > INTEGER DEFAULT 0); > > CREATE TABLE t2 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b > INTEGER DEFAULT 0); > CREATE INDEX t2_nx ON t2 (a); > > CREATE TABLE t3 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b > INTEGER DEFAULT 0); > CREATE UNIQUE INDEX t3_ux ON t3 (a); > > @array = qw(find many more some that this what when where which with > and for); > $sql1 = "SELECT COUNT(*) FROM web2 WHERE word LIKE ?"; > $sql2 = "SELECT COUNT(*) FROM web2 WHERE word = ?"; > > I want to SELECT COUNT(*) of each element in array using the sql > statements. > Benchmarking pseudo-code follows -- > > $iterations = 10 > > Benchmark: timing "LIKE" 10 iterations of t1, t2, t3... > t1: 129 wallclock secs (105.66 usr + 16.68 sys = 122.34 CPU) @ > 0.08/s (n=10) > t2: 130 wallclock secs (105.21 usr + 17.16 sys = 122.37 CPU) @ > 0.08/s (n=10) > t3: 131 wallclock secs (105.34 usr + 17.11 sys = 122.45 CPU) @ > 0.08/s (n=10) > > > --------------------------------------------- > Benchmark: timing "=" 10 iterations of t1, t2, t3... > t1: 0 wallclock secs ( 0.04 usr + 0.00 sys = 0.04 CPU) > @ 250.00/s > (n=10) > (warning: too few iterations for a reliable count) > t2: 0 wallclock secs ( 0.04 usr + 0.00 sys = 0.04 CPU) > @ 250.00/s > (n=10) > (warning: too few iterations for a reliable count) > t3: 1 wallclock secs ( 0.04 usr + 0.00 sys = 0.04 CPU) > @ 250.00/s > (n=10) > (warning: too few iterations for a reliable count) > > Seems, at least in the cases above, the INDEXes themselves don't make > any difference to performance worth writing home about. > > The main difference is in LIKE versus =. > > Is there anything wrong that I am doing, or overlooking > something? How > can I improve the performance of the LIKE selects? > > > -- > Puneet Kishor > >
