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> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]