Re: Optimizing table (shall I create a primary field?)

["Charles Lambach" <charles.lambach@xxxxxxxxx>]

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@xxxxxxxxx> wrote:
>
> On Sun, Apr 27, 2008 at 3:59 AM, Charles Lambach
> <charles.lambach@xxxxxxxxx> 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@xxxxxxxxx
> wultsch (aim)
>