Op zaterdag 11 juni 2005 17:04, schreef Cory Nelson: > 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
You can replace LIKE by example field>'Aa' and field<'Ab' finds all words that begin with ' Aa' and is able to use an index -- Met vriendelijke groet Bert Verhees ROSA Software
