At 15:53 -0500 10/30/05, [EMAIL PROTECTED] wrote:
"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
Several changes to join processing were made in 5.0.2 for compliance with
standard SQL. You can read about implications of these changes here:
http://dev.mysql.com/doc/refman/5.0/en/join.html
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
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
