List:General Discussion« Previous MessageNext Message »
From:Sergei Golubchik Date:November 17 2001 10:48pm
Subject:Re: many similar requests -> 1 request
View as plain text  
Hi!

On Nov 17, Sergei Golubchik wrote:
> Hi!
> 
> On Nov 17, boris hajduk wrote:
> > Saturday, November 17, Bruce Ferrell wrote:
> > 
> > BF> SELECT position, title FROM WHERE position1 IN (1...200) OR position2 IN
> > BF> (1...200);
> > 
> > no, i wasn't clear enough, let's try it again.
> >
> > the table book contains :
> > 
> > |-- title -----|--position1--|--position2--|--lots of other fields---
> > | bravenewworld|          0  |             |
> > | foundation   |          1  |             |
> > | shortstories |          2  |          3  |
> > | neuromancer  |          4  |          7  |
> > | hyperion     |          8  |          6  |
> > | salem        |          5  |             |
> > | lovedeath    |         11  |          9  |
> 
> And what's the problem ?
> 
> $_=join(",",(1..200));
> $result=mysql_query( << AAAA );
> SELECT IFNULL(position2,position1) as position,title
>       WHERE position1 IN ($_) OR position2 IN ($_) ORDER BY position;
> AAAA

Correction - this one is wrong, it will list each entry only once.

> You may change IFNULL to IF, if you use another 'dummy' value.
> 
> Still, it won't be very fast as MySQL cannot use _two_ indexes at one.
> If you don't mind using MySQL 4.0 you can use UNION:
> 
> $_=join(",",(1..200));
> $result=mysql_query( << AAAA );
> SELECT position1 as position,title WHERE position1 IN ($_) UNION
> SELECT position2 as position,title WHERE position2 IN ($_) ORDER BY position;
> AAAA

This one is ok.

> This way MySQL will resolve both SELECT with indexes.
> 
> Regards,
> Sergei
> 
Regards,
Sergei

-- 
MySQL Development Team
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/
Thread
many similar requests -> 1 requestboris hajduk17 Nov
  • Re: many similar requests -> 1 requestBruce Ferrell17 Nov
    • Re: many similar requests -> 1 requestboris hajduk17 Nov
      • Re: many similar requests -> 1 requestSergei Golubchik17 Nov
        • Re: many similar requests -> 1 requestSergei Golubchik17 Nov
          • Re: many similar requests -> 1 requestboris hajduk18 Nov
            • Re: many similar requests -> 1 requestSergei Golubchik18 Nov
              • Re[2]: many similar requests -> 1 requestboris hajduk18 Nov
Re: many similar requests -> 1 requestAnvar Hussain K.M.19 Nov