----- Original Message -----
From: Jules Bean <jules@stripped>
Sent: Tuesday, August 24, 1999 6:22 AM
Subject: Re: Performance issue - table scan on large table
[snip]
> But queries still take about 1 minute.
>
> I'm doing queries of the form SELECT count(*) FROM my_table WHERE
> my_field LIKE '%dog%';
>
> But any query which hits (mostly) every row has the same speed.
For comparison purposes..
I (currently) use like "%keyword%" (often multiple AND'ed keywords) on a
table where the .ISD is ~36MB (29,903 rows). The system is a PII-350 w/128MB
RAM, Ultra2-SCSI HD, Linux 2.0.36, MySQL 3.22.21.
Variables @ startup:
record_buffer='300K'
join_buffer='300K'
key_buffer='2M'
sort_buffer='2M'
table_cache='128'
Using 3 AND'ed LIKE's with a eq_ref JOIN on another table:
[like_count] [ 1.513582 ] Q/min [39]
[like] [ 1.532421 ] Q/min [39]
[instr_count] [ 1.6651505 ] Q/min [36]
[instr] [ 1.671847 ] Q/min [35]
[regexp_count] [ 2.3141075 ] Q/min [25]
[regexp] [ 2.3282345 ] Q/min [25]
So extrapolated out, if I had 9.4 million rows my queries would take about 5
minutes. Also - in all* the tests I've run searching for '%substring%' text,
LIKE has performed the best.
-Jay J
