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> > > > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "YL" <[EMAIL PROTECTED]> > Cc: <mysql@lists.mysql.com> > Sent: Sunday, October 30, 2005 1:53 PM > Subject: Re: 5.0.1 vs 5.0.15: view performance > > > "YL" <[EMAIL PROTECTED]> wrote on 10/30/2005 10:24:24 AM: > > > > > Dear list, I need some inputs/help on my finding below: > > > > > > 5.0.15 make my view (below) almost useless compare with 5.0.1-alpha: > > > > > > with the same data set, 5.0.15 took 18min but 5.0.1 took 6.3sec to get > > > the result: > > > mysql>select count(1) from unit_address; > > > +----------+ > > > | count(1) | > > > +----------+ > > > | 1438 | > > > +----------+ > > > Also 5.0.15 took 100% of my CPU and make the machine not responsive > > > to any requests! > > > > > > Maybe my whole approach was no good? see below (notice that the number > > > of rows are not so big at all!) > > > > > > create view unit_address as > > > 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, address t1, enterprise_unit t2 > > > Where t0.address_id = t1.address_id and t0.owner_class='EnterpriseUnit' > > > and t0.owner_id = t2.unit_id; > > > > > > mysql> desc enterprise_unit; > > > +---------------+-----------------------+------+-----+---------+-------+ > > > | Field | Type | Null | Key | Default | Extra | > > > +---------------+-----------------------+------+-----+---------+-------+ > > > | name | varchar(80) | YES | | NULL | | > > > | unit_id | mediumint(8) unsigned | NO | | 0 | | > > > | property_dict | text | YES | | NULL | | > > > | type_id | smallint(5) unsigned | YES | | NULL | | > > > | parent_id | mediumint(8) unsigned | YES | | NULL | | > > > | status_code | tinyint(4) | YES | | NULL | | > > > | gb_name | varchar(80) | YES | | NULL | | > > > | b5_name | varchar(80) | YES | | NULL | | > > > | path | varchar(80) | YES | | NULL | | > > > +---------------+-----------------------+------+-----+---------+-------+ > > > mysql> desc address; > > > +-------------+-----------------------+------+-----+---------+-------+ > > > | Field | Type | Null | Key | Default | Extra | > > > +-------------+-----------------------+------+-----+---------+-------+ > > > | city | varchar(48) | YES | | NULL | | > > > | country_id | smallint(5) unsigned | YES | | NULL | | > > > | county | varchar(36) | YES | | NULL | | > > > | address_id | int(11) | YES | | NULL | | > > > | status_code | tinyint(4) | YES | | NULL | | > > > | street | text | YES | | NULL | | > > > | zip | varchar(12) | YES | | NULL | | > > > | state_id | mediumint(8) unsigned | YES | | NULL | | > > > | zip_ext | varchar(8) | YES | | NULL | | > > > +-------------+-----------------------+------+-----+---------+-------+ > > > mysql> desc address_association; > > > > > +----------------+-----------------------+------+-----+---------+-------+ > > > | Field | Type | Null | Key | Default | Extra > > | > > > > > +----------------+-----------------------+------+-----+---------+-------+ > > > | address_id | mediumint(8) unsigned | YES | | NULL | | > > > | association_id | int(10) unsigned | NO | | 0 | | > > > | property_dict | text | YES | | NULL | | > > > | type_id | smallint(5) unsigned | YES | | NULL | | > > > | owner_id | mediumint(8) unsigned | YES | | NULL | | > > > | owner_class | varchar(32) | YES | | NULL | | > > > | status_code | tinyint(4) | YES | | NULL | | > > > | flag | varchar(64) | YES | | NULL | | > > > > > +----------------+-----------------------+------+-----+---------+-------+ > > > mysql> select count(1) from address; > > > +----------+ > > > | count(1) | > > > +----------+ > > > | 1588 | > > > +----------+ > > > mysql> select count(1) from enterprise_unit; > > > +----------+ > > > | count(1) | > > > +----------+ > > > | 1444 | > > > +----------+ > > > mysql> select count(1) from address_association; > > > +----------+ > > > | count(1) | > > > +----------+ > > > | 1456 | > > > +----------+ > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > Something I recently gleaned by lurking on the INTERNALs list is that the > > comma operator is scheduled to evaluate AFTER explicit inner joins. I > > don't think that your SQL statement is efficiently declaring your view. > > Please try the EXPLICITLY JOINed version of your select statement and > > verify that an EXPLAIN on your statement still shows that you are using > > the indexes you wanted used in the first place. > > > > If it works fast as a stand-alone SELECT statement, it will be fast as a > > VIEW, too. > > > > 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'; > > > > How fast does that query work and what is the EXPLAIN for it? > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: 10/28/2005
