List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 31 2005 2:22am
Subject:Re: 5.0.1 vs 5.0.15: view performance
View as plain text  
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
Thread
5.0.1 vs 5.0.15: view performanceYL30 Oct
  • Re: 5.0.1 vs 5.0.15: view performanceSGreen30 Oct
    • Re: 5.0.1 vs 5.0.15: view performanceYL31 Oct
      • Re: 5.0.1 vs 5.0.15: view performanceSGreen31 Oct
        • Re: 5.0.1 vs 5.0.15: view performanceYL31 Oct
          • Re: 5.0.1 vs 5.0.15: view performanceSGreen31 Oct
            • Re: 5.0.1 vs 5.0.15: view performanceYL31 Oct
            • Re: 5.0.1 vs 5.0.15: view performanceJeremiah Gowdy31 Oct
      • Connect fails but only from this one file WHYGerald Taylor31 Oct
        • Re: Connect fails but only from this one file WHYMichael Stassen31 Oct
        • Re: Connect fails but only from this one file WHYGleb Paharenko31 Oct
Re: 5.0.1 vs 5.0.15: view performancePaul DuBois30 Oct