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 <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
