List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:April 27 2008 12:21pm
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
On Sun, Apr 27, 2008 at 3:59 AM, Charles Lambach
<charles.lambach@stripped> wrote:
>  I do _always_ this query:
>  SELECT * FROM books WHERE isbn='foo' LIMIT 1
>
>  The primary key of this table was 'id', and 'isbn' was and INDEX field.

This sentence could have been better written. If you have a primary
key index on (id,isbn) and isbn is not indexed as the left most column
of another index then an index would not be used for the above query.
If you have a prymary key index on (id) and another index on ('isbn')
then that index would probably be used.

It would much easier to tell you whats going on if you post your DDL
(so post the output of 'SHOW CREATE TABLE books;') and your EXPLAIN
(so post the output of 'EXPLAIN SELECT * FROM books WHERE isbn='foo'
LIMIT 1;').

-- 
Rob Wultsch
wultsch@stripped
wultsch (aim)
Thread
Optimizing table (shall I create a primary field?)Charles Lambach27 Apr
  • Re: Optimizing table (shall I create a primary field?)Rob Wultsch27 Apr
    • Re: Optimizing table (shall I create a primary field?)Charles Lambach28 Apr
      • Re: Optimizing table (shall I create a primary field?)Rob Wultsch28 Apr
        • Re: Optimizing table (shall I create a primary field?)Rob Wultsch28 Apr
          • Re: Optimizing table (shall I create a primary field?)Wm Mussatto28 Apr
            • Re: Optimizing table (shall I create a primary field?)Charles Lambach29 Apr
              • Re: Optimizing table (shall I create a primary field?)Rob Wultsch29 Apr
  • Re: Optimizing table (shall I create a primary field?)Sebastian Mendel30 Apr