SEO

Hi.

My hosting provider recommended me to optimize my 200,000 record table in
order to save resources.

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.

I've modified this:
ALTER TABLE books DROP PRIMARY KEY, ADD INDEX ('isbn')
ALTER TABLE books ADD PRIMARY KEY ('isbn')

Is this a good change? Am I going to waste less resources with 'isbn' field
as primary key?

Thank you very much.

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

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

I am going to assume that you are asking this question because
performance has not improved from this change. Is this correct?

I don't think that your surogate key (id) is useful, but that is
probably minor. I think that your hostings company suggestion is
probably a good idea, but will also probably not impact performance. A
prymary key is basically only a unique not null key, and it sounds
like your isbn field should have those properties.

>key_len=302
This is pretty horrible. If I needed better performance I would try to
turn the isbn field into a bigint (the dashes are the only non-numeric
characters and have no significance, right?). That would start with
something like:
ALTER TABLE `books` ADD `new_isbn` BIGINT NOT NULL ;
UPDATE `books` SET `new_isbn` = replace(`isbn`,'-','');
....
though that may require significant changes in your app (converting
the string into an int) but a stored procedure might make this
somewhat less painful.

What are your goals here?

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

On Mon, Apr 28, 2008 at 6:49 AM, Rob Wultsch <wultsch@gmail.com> wrote:
> I am going to assume that you are asking this question because
> performance has not improved from this change. Is this correct?
>
> I don't think that your surogate key (id) is useful, but that is
> probably minor. I think that your hostings company suggestion is
> probably a good idea, but will also probably not impact performance. A
> prymary key is basically only a unique not null key, and it sounds
> like your isbn field should have those properties.
>
> >key_len=302
> This is pretty horrible. If I needed better performance I would try to
> turn the isbn field into a bigint (the dashes are the only non-numeric
> characters and have no significance, right?). That would start with
> something like:
> ALTER TABLE `books` ADD `new_isbn` BIGINT NOT NULL ;
> UPDATE `books` SET `new_isbn` = replace(`isbn`,'-','');
> ...
> though that may require significant changes in your app (converting
> the string into an int) but a stored procedure might make this
> somewhat less painful.
>
> What are your goals here?

For the record: if performance is good enough I wouldn't change anything...

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

On Mon, April 28, 2008 09:44, Rob Wultsch wrote:
> On Mon, Apr 28, 2008 at 6:49 AM, Rob Wultsch <wultsch@gmail.com> wrote:
>> I am going to assume that you are asking this question because
>> performance has not improved from this change. Is this correct?
>>
>> I don't think that your surogate key (id) is useful, but that is
>> probably minor. I think that your hostings company suggestion is
>> probably a good idea, but will also probably not impact performance. A
>> prymary key is basically only a unique not null key, and it sounds
>> like your isbn field should have those properties.
>>
>> >key_len=302
>> This is pretty horrible. If I needed better performance I would try to
>> turn the isbn field into a bigint (the dashes are the only non-numeric
>> characters and have no significance, right?). That would start with
>> something like:
>> ALTER TABLE `books` ADD `new_isbn` BIGINT NOT NULL ;
>> UPDATE `books` SET `new_isbn` = replace(`isbn`,'-','');
>> ...
>> though that may require significant changes in your app (converting
>> the string into an int) but a stored procedure might make this
>> somewhat less painful.
>>
>> What are your goals here?
>
> For the record: if performance is good enough I wouldn't change
> anything...
>
> --
> Rob Wultsch
> wultsch@gmail.com
> wultsch (aim)
>
ISBN field is way too long, I think they just changed it to 13 characters.
Depending on your application leading '0' may be important so you may be
stuck with a character field. As was suggested, loose the '-' and spaces.
I don't think they are standard and I would think its easier to
universally remove them.
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

Thank you all for your suggestions.

So it's very important to make primary fields be as smaller as possible,
right? I'm going to change 'isbn' from VARCHAR(100) to VARCHAR(25) and, if
possible (I might change my code), from VARCHAR to BIGINT.

By the way, which are optimal values for "key_len" parameter when doing
EXPLAIN?

Regards,
--Charles



ISBN field is way too long, I think they just changed it to 13 characters.
> Depending on your application leading '0' may be important so you may be
> stuck with a character field. As was suggested, loose the '-' and spaces.
> I don't think they are standard and I would think its easier to
> universally remove them.
> ------
> William R. Mussatto
> Systems Engineer
> http://www.csz.com
> 909-920-9154
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=c...bach@gmail.com
>
>

On Tue, Apr 29, 2008 at 5:09 AM, Charles Lambach
<charles.lambach@gmail.com> wrote:
> Thank you all for your suggestions.
>
> So it's very important to make primary fields be as smaller as possible,
> right? I'm going to change 'isbn' from VARCHAR(100) to VARCHAR(25) and, if
> possible (I might change my code), from VARCHAR to BIGINT.
>
> By the way, which are optimal values for "key_len" parameter when doing
> EXPLAIN?
>
> Regards,
> --Charles
>
"The key_len column indicates the length of the key that MySQL decided
to use. The length is NULL if the key column says NULL. Note that the
value of key_len enables you to determine how many parts of a
multiple-part key MySQL actually uses."
http://dev.mysql.com/doc/refman/5.0/...g-explain.html

Note: that is length in bytes.

I don't know how much faster your queries will get by reducing the
varchar size, I have not had to deal with many performance issues of
this type. If it is effective (you are noting query times, right?) you
should be able to further reduce the size by change the character set
from multi-btye utf-8 to single byte ascii. The query would be
something like:
ALTER TABLE `books` CHANGE `isbn` `isbn` VARCHAR( 25) CHARACTER SET
ascii NOT NULL

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Charles Lambach schrieb:
> Hi.
>
> My hosting provider recommended me to optimize my 200,000 record table in
> order to save resources.
>
> 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.
>
> I've modified this:
> ALTER TABLE books DROP PRIMARY KEY, ADD INDEX ('isbn')
> ALTER TABLE books ADD PRIMARY KEY ('isbn')
>
> Is this a good change? Am I going to waste less resources with 'isbn' field
> as primary key?

IMO not, but this depends on your app,

the Primary Key should be a value that never changes in lifetime of a row,
and should never be re-used once deleted

if you ever happen to change your ISBN cause by a typo or something, than
your references to other tables need to be updated too

having `id` as primary key is good
and leave the ISBN unique

you can cut down the index length by half the ISBN length, this should be
more than enough

according to http://en.wikipedia.org/wiki/Interna...rd_Book_Number
you can use a fixed width unsigned INT field with a length of 13 for your ISBN

but you will loose formating ...

or you use two fields, one with formated ISBN and one indexed with numeric ISBN

--
Sebastian Mendel