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>
>
>
> ----- Original Message -----
> From: <SGreen@stripped>
> To: "YL" <elim@stripped>
> Cc: <mysql@stripped>
> Sent: Sunday, October 30, 2005 1:53 PM
> Subject: Re: 5.0.1 vs 5.0.15: view performance
>
> > "YL" <elim@stripped> 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/mysql?unsub=1
> > >
> >
> >
> > 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
