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
