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
