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: <SGreen@stripped>
To: "YL" <elim@stripped>
> 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" <elim@stripped> 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>
