List:General Discussion« Previous MessageNext Message »
From:Sergei Golubchik Date:November 17 2001 10:45pm
Subject:Re: many similar requests -> 1 request
View as plain text  
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

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 way MySQL will resolve both SELECT with indexes.

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