List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 2 2000 6:03pm
Subject:RE: Explain type 'ALL'.
View as plain text  
At 5:40 PM +0000 02-02-2000, James Gray wrote:
>You can't use an index if you have an OR!!!
>Please tell me I've miss-understood this, can I work around it???

Break your query into two separate queries so that you don't have an
OR in it.


>
>-----Original Message-----
>From: sasha@stripped [mailto:sasha@stripped]On
>Behalf Of sasha@stripped
>Sent: 02 February 2000 16:33
>To: james.gray@stripped
>Cc: mysql@stripped
>Subject: Re: Explain type 'ALL'.
>
>
>James Gray wrote:
>>
>>  I am looking at the result of an "explain select" that does *not* perform
>a
>>  join,
>>  I'm getting a 'ALL' in 'type' - the documentation describes this as very
>bad
>>  but I'm not performing a join, is this still very bad?
>>
>>  Thanks:
>>
>>  SQL-query:
>>  EXPLAIN SELECT
>BOK_ISBN,BOK_AUTHOR,BOK_TITLE,BOK_PUB,BOK_DATPUB,BOK_BINCODE
>>  FROM BOOKS WHERE BOK_AUTHOR LIKE 'bond%' AND BOK_AVAIL='ip' || BOK_TITLE
>>  LIKE 'bond%' AND BOK_AVAIL='ip';
>>
>>  table   type    possible_keys           key     key_len         ref
>rows            Extra
>>  BOOKS           ALL   BOK_TITLE,BOK_AUTHOR
>1041289         where used
>
>The problems is that MySQL optimizer cannot yet use an index if you have an
>OR.
>
>--
>Sasha Pachev
>
>+------------------------------------------------------------------+
>| TcX  ____  __     _____   _____  ___     ==  mysql@stripped        |
>|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sasha Pachev            |
>|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sasha@stripped  |
>|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Provo, Utah, USA        |
>|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                         |
>|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                        |
>|             /*/             \*\ Developers Team                  |
>+------------------------------------------------------------------+
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread26329@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-paul=snake.net@stripped>


-- 
Paul DuBois, paul@stripped
Thread
Explain type 'ALL'.James Gray2 Feb
  • Re: Explain type 'ALL'.Matthias Urlichs2 Feb
  • Re: Explain type 'ALL'.sasha2 Feb
RE: Explain type 'ALL'.James Gray2 Feb
  • Re: Explain type 'ALL'.sasha2 Feb
  • RE: Explain type 'ALL'.Paul DuBois2 Feb
RE: Explain type 'ALL'.James Gray2 Feb
  • DELETETony) (Nestor2 Feb
    • Re: DELETEMr. Anthony R.J. Ball2 Feb
    • Re: DELETEDaniel Beckham2 Feb
    • Re: DELETEBlain Nelson2 Feb
  • RE: Explain type 'ALL'.Paul DuBois2 Feb
RE: Explain type 'ALL'.James Gray2 Feb