"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
