On 6/11/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: > 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?
You're not doing anything wrong. LIKE isn't able to use an index, so it must scan the entire table. > > -- > Puneet Kishor > > -- Cory Nelson http://www.int64.org
