Re: Re: MySQL Select Speed Optimisation

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

Comment on Re: Re: MySQL Select Speed Optimisation Download Code

Replies are listed 'Best First'.
Re: Re: Re: MySQL Select Speed Optimisation by IlyaM (Parson) on Mar 27, 2003 at 11:36 UTC
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	[reply]
Re (3): MySQL Select Speed Optimisation by VSarkiss (Monsignor) on Mar 27, 2003 at 15:59 UTC
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.	[reply] [d/l] [select]