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> >
