The primary key by definition is an index ("key"). If you don't make it a
primary key index or unique index then duplicate entries can occur. It is
good practice to always set a primary key, and always make it a unique
auto-increment integer.
If you look at the output of EXPLAIN now, you can see your table using the
indexes to perform the query.
----- Original Message -----
From: "YL" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Sunday, October 30, 2005 11:16 PM
Subject: Re: 5.0.1 vs 5.0.15: view performance
Thanks a lot Shawn: After adding index, it's amazingly faster(< 0.15 sec,
was > 16 min !).
I thought index columns would be necessary only for tables have 10,000 rows
or more but I was so wrong.
address_id, association_id and unit_id are PKs for tables Address,
Address_Association and Enterprise_Unit respectively and I have a table
eo_pk_table to store the max pk of each table, which is maintained by the
application to figure out the next key and set the foreign key for new
records and keep the uniqueness of records. Now tables were modified and PKs
became explicit at database level. Does this help the performance?
association_id is not refered very often, but address_id is. so I indexed
address_id in address_association.
Is it a good idea to index PKs for big table?
Thanks again,
YL
mysql> show create table address\G;
*************************** 1. row ***************************
Table: address
Create Table: CREATE TABLE `address` (
.....
`county` varchar(36) default NULL,
`address_id` int(10) unsigned NOT NULL default '0',
.....
`zip_ext` varchar(8) default NULL,
PRIMARY KEY (`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> show create table address_association\G;
*************************** 1. row ***************************
Table: address_association
Create Table: CREATE TABLE `address_association` (
.....
`association_id` int(10) unsigned NOT NULL default '0',
.....
PRIMARY KEY (`association_id`),
KEY `owner_class` (`owner_class`,`owner_id`,`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> show create table enterprise_unit\G;
*************************** 1. row ***************************
Table: enterprise_unit
Create Table: CREATE TABLE `enterprise_unit` (
.....
`unit_id` mediumint(8) unsigned NOT NULL default '0',
.....
PRIMARY KEY (`unit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Thanks again
----- Original Message -----
From: [EMAIL PROTECTED]
To: YL
Cc: mysql@lists.mysql.com
Sent: Sunday, October 30, 2005 9:25 PM
Subject: Re: 5.0.1 vs 5.0.15: view performance
As I suspected, you have no indexes. You didn't even define a primary key
(PK).
For each table, decide which column or combination of columns you can use
to uniquely identify each row. Make that your PRIMARY KEY for each table.
For other columns or combinations of columns you frequently use in your
queries, create INDEXes (KEYs) on them to speed up your query responses.
I suspect that on your `address` table, the `addresss_id` column is unique
for each row. To mark it as the primary key for the `address` table you
could use this command
ALTER TABLE `address` ADD PRIMARY KEY(`address_id`);
The table `address_association` should probably only contain a single row
for any (address_id, association_id) combination. That would make those two
columns the likely candidate for acting as the PRIMARY KEY for that table.
You would declare such a key as
ALTER TABLE `adress_association` ADD PRIMARY KEY(`address_id`,
`association_id`);
You should perform the same analysis for all of your other tables. Very
rarely is it ever a good design to allow duplicates of entire rows within
the same table. There should always be something that makes one row unique
from every other row in the same table.
In your case, an additional index on several columns of
`address_association` will make your particular view much faster.
ALTER TABLE `address_association` ADD KEY(`owner_class`,`owner_id`,
`association_id`);
You need to add all of the appropriate indexes to all of your tables.
Then, look at another EXPLAIN of your SELECT statement, the KEY column
should be filled in for at least 2 of your tables. With the tiny number of
rows you have in your database you should be seeing results returned in less
than 0.05 seconds (even if you have an 1980's machine). It's the complete
lack of indexes that has killed your performance.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"YL" <[EMAIL PROTECTED]> wrote on 10/30/2005 10:21:39 PM:
> Thanks Shawn for the help: The same query took 2min less than before on
> 5.0.15 after
> using inner join. Is what you ask:
>
> mysql> show create table address\G;
> *************************** 1. row ***************************
> Table: address
> Create Table: CREATE TABLE `address` (
> `city` varchar(48) default NULL,
> `country_id` smallint(5) unsigned default NULL,
> `county` varchar(36) default NULL,
> `address_id` int(10) unsigned NOT NULL default '0',
> `status_code` tinyint(4) default NULL,
> `street` text,
> `zip` varchar(12) default NULL,
> `state_id` mediumint(8) unsigned default NULL,
> `zip_ext` varchar(8) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.01 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table address_association\G;
> *************************** 1. row ***************************
> Table: address_association
> Create Table: CREATE TABLE `address_association` (
> `address_id` mediumint(8) unsigned default NULL,
> `association_id` int(10) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `owner_id` mediumint(8) unsigned default NULL,
> `owner_class_name` varchar(32) default NULL,
> `status_code` tinyint(3) unsigned default NULL,
> `flag` varchar(16) default 'default'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table enterprise_unit\G;
> *************************** 1. row ***************************
> Table: enterprise_unit
> Create Table: CREATE TABLE `enterprise_unit` (
> `name` varchar(80) default NULL,
> `unit_id` mediumint(8) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `parent_id` mediumint(8) unsigned default NULL,
> `status_code` tinyint(4) default NULL,
> `gb_name` varchar(80) default NULL,
> `b5_name` varchar(80) default NULL,
> `path` varchar(80) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> From: <[EMAIL PROTECTED]>
> To: "YL" <[EMAIL PROTECTED]>
>
>
> > You just identified the reason this is SO slow. You seem to have no
> > indexes. Can you post the results of
> >
> > SHOW CREATE TABLE address_association\G
> > SHOW CREATE TABLE address\G
> > SHOW CREATE TABLE enterprise_unit\G
> >
> > That will let me know exactly what is and isn't already indexed.
> >
> > Thanks!
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> > "YL" <[EMAIL PROTECTED]> wrote on 10/30/2005 08:23:14 PM:
> >
> > > Thanks SGreen's help. I don't know much about how to use the result
> > > of 'Explain'
> > > but here it is
> > > mysql> explain
> > > -> select t0.association_id
> > > -> , t0.property_dict as asso_property
> > > -> , t0.status_code as asso_status_code
> > > -> , t0.flag as asso_flag
> > > -> , t0.type_id as asso_type_id
> > > -> , t1.address_id,t1.city
> > > -> , t1.country_id
> > > -> , t1.county
> > > -> , t1.state_id
> > > -> , t1.status_code as addr_status_code
> > > -> , t1.street
> > > -> , t1.zip
> > > -> , t1.zip_ext
> > > -> , t2.name
> > > -> , t2.unit_id
> > > -> , t2.property_dict as unit_property
> > > -> , t2.type_id as unit_type_id
> > > -> , t2.parent_id as unit_parent_id
> > > -> , t2.status_code as unit_status
> > > -> , t2.gb_name
> > > -> , t2.b5_name
> > > -> , t2.path as unit_path
> > > -> FROM address_association t0
> > > -> INNER JOIN address t1
> > > -> ON t0.address_id = t1.address_id
> > > -> INNER JOIN enterprise_unit t2
> > > -> ON t0.owner_id = t2.unit_id
> > > -> WHERE t0.owner_class='EnterpriseUnit';
> > > +----+-------------+-------+------+---------------+------+---------
> > > +------+------+-------------+
> > > | id | select_type | table | type | possible_keys | key | key_len |
> > > ref | rows | Extra |
> > > +----+-------------+-------+------+---------------+------+---------
> > > +------+------+-------------+
> > > | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL |
> > > NULL | 1588 | |
> > > | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL |
> > > NULL | 1444 | |
> > > | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL |
> > > NULL | 1456 | Using where |
> > > +----+-------------+-------+------+---------------+------+---------
> > > +------+------+-------------+
> > > 3 rows in set (0.11 sec)
> > >
> > > mysql>
>
------------------------------------------------------------------------------
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: 10/28/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
