The "OR" operator will slow down your query mercilessly. However, using the "IN" operator, MySQL will use the primary key (or any associated index) and run very fast.

SELECT * FROM some_table WHERE key IN ( ?, ?, ?);
[download]

An alternative to using placeholders is to prepare this statement using a join.

my @searchkeys = qw(a b c d);
my $query = qq{
    SELECT * FROM some_table WHERE key IN (
    }
    . join(",", map( {$dbh->quote($_)} @searchkeys))
    . qq{)};
[download]

update (1). See also Using OR in SELECT statments safely.

update (2). Some benchmarking, to show that the IN operator is truly faster. I ran the query against a large table containing 500,000 records.

#!/usr/bin/perl -w
use DBI;
use strict;
use Benchmark;

my $dbh = DBI->connect("DBI:mysql:chess_db;host=localhost"
    . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
    undef, undef, {RaiseError => 1})
    or die "can't connect\n";

my $query = qq{SELECT count(*) from PGN_base_data where };

my @searchkeys =  ((100_001 .. 100_800));

my $or_query = $query
   . join( " OR " , map( { "ID = $_"} @searchkeys));

my $in_query = $query
   . "ID IN ("
   . join( "," , @searchkeys)
   . ")";

my $sth_or = $dbh->prepare($or_query);
my $sth_in = $dbh->prepare($in_query);

sub get_it {
    my $sth = shift;
    $sth->execute;
    my $result = $sth->fetchrow_arrayref;
    $sth->finish;
    #print $result->[0], "\n";;
}

timethese (2000,
    {
        'or' => sub {get_it $sth_or},
        'in' => sub {get_it $sth_in}
    });

$dbh->disconnect();

__END__
with 400 keys in the query
Benchmark: timing 4000 iterations of in, or...
    in: 17 wallclock secs ( 0.37 usr +  0.09 sys =  0.46 CPU)
    or: 52 wallclock secs ( 0.70 usr +  0.08 sys =  0.78 CPU)

with 800 keys in the query
Benchmark: timing 2000 iterations of in, or...
    in: 17 wallclock secs ( 0.30 usr +  0.10 sys =  0.40 CPU)
    or: 95 wallclock secs ( 0.41 usr +  0.06 sys =  0.47 CPU)
[download]

Note. There is no possible comparison with UNION, because it would return several rows, which should be added up in the client.
My test query is only asking for a COUNT(*), thus returning only one row.

 _  _ _  _  
(_|| | |(_|><
 _|   

I am caching the $sth which are generated with a $dbh->prepare_cached( $sql ) and these get used over and over again (it is a long running daemon not a CGI) thus the desire to avoid the join syntax - as well as the quoting issues. I have read up on IN which is not (currently) part of my limited SQL syntax and it looks like just the ticket. I'll benchmark it tomorrow. Thanks.

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

Note that prepared queries are only supported by MySQL since 4.1.0. Moreover I'm not sure DBD::mysql supports new API for prepared queries. It is very likely that prepare_cached in your case doesn't give any perfomance benefits over raw prepare.

--
Ilya Martynov, ilya@iponweb.net
CTO IPonWEB (UK) Ltd
Quality Perl Programming and Unix Support UK managed @ offshore prices - http://www.iponweb.net
Personal website - http://martynov.org

I'm not sure about MySQL, but some optimizers will just interpret IN as a set of OR clauses. If the number of choices is fixed, another alternative is to do three queries, or, to also keep the number of round-trips to the database down, form a UNION of them:

SELECT * FROM table WHERE key = ?
UNION ALL
SELECT * FROM table WHERE key = ?
UNION ALL
SELECT * FROM table WHERE key = ?
[download]

YMMV greatly; measuring is the only way to tell whether this will help or hurt.

It looks like gmax nailed your specific question, so I'll toss in a bit of general advice. For playing in the SQL optimization sandbox, I've found Gulutzan and Pelzer's SQL Performance Tuning to be quite useful. It discusses the general theory of how RDBMSs work under the covers (including details on cost-based query optimization and the performance characteristics of various table implementations), then contrasts how 8 different RDMBSes (including MySQL, Oracle, and SQL Server) respond to various query adjustments and optimizations. It's great fodder for reasoning through performance problems.

Update: This isn't what you asked: I misread your question.

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
[download]