List:General Discussion« Previous MessageNext Message »
From:Jay J Date:August 24 1999 12:20pm
Subject:Re: Performance issue - table scan on large table
View as plain text  
----- 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

Thread
Performance issue - table scan on large tableJules Bean9 Aug
  • Performance issue - table scan on large tablesinisa9 Aug
  • Re: Performance issue - table scan on large tableJules Bean9 Aug
    • Re: Performance issue - table scan on large tableBenjamin Pflugmann10 Aug
  • Re: Performance issue - table scan on large tableRichard K. Moore9 Aug
    • Re: Performance issue - table scan on large tableJules Bean10 Aug
      • Re: Performance issue - table scan on large tableMichael Widenius12 Aug
    • Re: Performance issue - table scan on large tableJules Bean24 Aug
    • Re: Performance issue - table scan on large tableJules Bean24 Aug
    • Re: Performance issue - table scan on large tableJay J24 Aug