Re: Optimizing table (shall I create a primary field?)
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/[EMAIL PROTECTED]
How to know the maximum length of a field
Hi. I've got a table with some fields, which I created with VARCHAR(100). I want to optimize this table, and I want to modify the length of these fields from VARCHAR(100) to VARCHAR(maximum_length), where maximum_length is the length of the record with the longest field. I could create a Perl script to do it, but I wonder if there's a mySQL command which does it automatically. Thank you very much, --Charles
Re: Optimizing table (shall I create a primary field?)
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 [EMAIL PROTECTED] wrote:
On Sun, Apr 27, 2008 at 3:59 AM, Charles Lambach
[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
wultsch (aim)
Optimizing table (shall I create a primary field?)
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.
