List:General Discussion« Previous MessageNext Message »
From:Charles Lambach Date:April 28 2008 9:28am
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
Hi Rob.

Thank you very much for your answer.

CREATE TABLE `books` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(200) NOT NULL,
  `author_name` varchar(100) NOT NULL,
  `category_name` varchar(100) NOT NULL,
  `description` varchar(200) NOT NULL,
  `isbn` varchar(100) NOT NULL,
  PRIMARY KEY  (`isbn`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=227976 DEFAULT CHARSET=utf8
AUTO_INCREMENT=227976 ;

-------------------------------------------------

EXPLAIN SELECT * FROM books WHERE isbn='978-0-19-280239-2' LIMIT 1

id=1
select_type=SIMPLE
table=books
type=const
possible_keys=PRIMARY
key=PRIMARY
key_len=302
ref=const
rows=1
Extra=

----------------------
Regards,
--Charles

On 4/27/08, Rob Wultsch <wultsch@stripped> wrote:
>
> 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