|
Home > Archive > MySQL ODBC Connector > October 2005 > 5.0.1 vs 5.0.15: view performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
5.0.1 vs 5.0.15: view performance
|
|
|
| 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='Enterpr
iseUnit'
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...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-10-30, 8:23 pm |
| --=_alternative 00728498852570AA_=
Content-Type: text/plain; charset="US-ASCII"
"YL" <elim@pdtnetworks.net> 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='Enterpr
iseUnit'
> 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=sgreen@unimin.com
>
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='Enterpr
iseUnit';
How fast does that query work and what is the EXPLAIN for it?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00728498852570AA_=--
| |
| Paul DuBois 2005-10-30, 8:23 pm |
| At 15:53 -0500 10/30/05, SGreen@unimin.com wrote:
>"YL" <elim@pdtnetworks.net> wrote on 10/30/2005 10:24:24 AM:
>
>+----------------+-----------------------+------+-----+---------+-------+
>|
>+----------------+-----------------------+------+-----+---------+-------+
>+----------------+-----------------------+------+-----+---------+-------+
>
>
>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='Enterpr
iseUnit';
>
>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/mysql? unsub...sie.nctu.edu.tw
| |
|
| ------ =_NextPart_000_00A4_
01C5DD7E.FDE9E870
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
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 =3D t1.address_id
-> INNER JOIN enterprise_unit t2
-> ON t0.owner_id =3D t2.unit_id
-> WHERE t0. owner_class=3D'Enter
priseUnit';
+----+-------------+-------+------+---------------+------+---------+-----=
-+------+-------------+
| 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 -----=20
From: <SGreen@unimin.com>
To: "YL" <elim@pdtnetworks.net>
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" <elim@pdtnetworks.net> wrote on 10/30/2005 10:24:24 AM:
>=20
get[color=darkred]
number[color=darkred
]
t0. owner_class=3D'Enter
priseUnit'[color=dar
kred]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
Extra |[color=darkred]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
|[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
> =
+----------------+-----------------------+------+-----+---------+-------+=
Extra=20[color=darkr
ed]
> |
> =
+----------------+-----------------------+------+-----+---------+-------+=
> =
+----------------+-----------------------+------+-----+---------+-------+=
http://lists.mysql.com/mysql? unsub...imi
n.com[color=darkred]
>=20
>=20
> Something I recently gleaned by lurking on the INTERNALs list is that =
the=20
> comma operator is scheduled to evaluate AFTER explicit inner joins. I=20
> don't think that your SQL statement is efficiently declaring your =
view.=20
> Please try the EXPLICITLY JOINed version of your select statement and=20
> verify that an EXPLAIN on your statement still shows that you are =
using=20
> the indexes you wanted used in the first place.
>=20
> If it works fast as a stand-alone SELECT statement, it will be fast as =
a=20
> VIEW, too.
>=20
> 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=20
> FROM address_association t0
> INNER JOIN address t1
> ON t0.address_id =3D t1.address_id
> INNER JOIN enterprise_unit t2
> ON t0.owner_id =3D t2.unit_id
> WHERE t0. owner_class=3D'Enter
priseUnit';
>=20
> How fast does that query work and what is the EXPLAIN for it?
>=20
> 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
------ =_NextPart_000_00A4_
01C5DD7E.FDE9E870--
| |
| SGreen@unimin.com 2005-10-31, 9:23 am |
| --=_alternative 000CDD37852570AB_=
Content-Type: text/plain; charset="US-ASCII"
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@pdtnetworks.net> 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='Enterpr
iseUnit';
> +----+-------------+-------+------+---------------+------+---------
> +------+------+-------------+
> | 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@unimin.com>
> To: "YL" <elim@pdtnetworks.net>
> Cc: <mysql@lists.mysql.com>
> Sent: Sunday, October 30, 2005 1:53 PM
> Subject: Re: 5.0.1 vs 5.0.15: view performance
>
get[color=darkred]
number[color=darkred
]
t0. owner_class='Enterpr
iseUnit'[color=darkr
ed]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
Extra |[color=darkred]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
+---------------+-----------------------+------+-----+---------+-------+[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
|[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
+-------------+-----------------------+------+-----+---------+-------+[color=darkred]
+----------------+-----------------------+------+-----+---------+-------+[color=darkred]
Extra[color=darkred]
+----------------+-----------------------+------+-----+---------+-------+[color=darkred]
+----------------+-----------------------+------+-----+---------+-------+[color=darkred]
the[color=darkred]
view.[color=darkred]
using[color=darkred]
a[color=darkred]
>
>
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date:
10/28/2005
--=_alternative 000CDD37852570AB_=--
| |
|
| Thanks Shawn for the help: The same query took 2min less than before on
5.0.15 after
using inner join. Is what you ask:
mysql> show create table address\G;
********************
******* 1. row ********************
*******
Table: address
Create Table: CREATE TABLE `address` (
`city` varchar(48) default NULL,
`country_id` smallint(5) unsigned default NULL,
`county` varchar(36) default NULL,
`address_id` int(10) unsigned NOT NULL default '0',
`status_code` tinyint(4) default NULL,
`street` text,
`zip` varchar(12) default NULL,
`state_id` mediumint(8) unsigned default NULL,
`zip_ext` varchar(8) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> show create table address_association\
G;
********************
******* 1. row ********************
*******
Table: address_association
Create Table: CREATE TABLE `address_association
` (
`address_id` mediumint(8) unsigned default NULL,
`association_id` int(10) unsigned NOT NULL default '0',
`property_dict` text,
`type_id` smallint(5) unsigned default NULL,
`owner_id` mediumint(8) unsigned default NULL,
`owner_class_name` varchar(32) default NULL,
`status_code` tinyint(3) unsigned default NULL,
`flag` varchar(16) default 'default'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show create table enterprise_unit\G;
********************
******* 1. row ********************
*******
Table: enterprise_unit
Create Table: CREATE TABLE `enterprise_unit` (
`name` varchar(80) default NULL,
`unit_id` mediumint(8) unsigned NOT NULL default '0',
`property_dict` text,
`type_id` smallint(5) unsigned default NULL,
`parent_id` mediumint(8) unsigned default NULL,
`status_code` tinyint(4) default NULL,
`gb_name` varchar(80) default NULL,
`b5_name` varchar(80) default NULL,
`path` varchar(80) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
From: <SGreen@unimin.com>
To: "YL" <elim@pdtnetworks.net>
[color=darkred]
> 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@pdtnetworks.net> wrote on 10/30/2005 08:23:14 PM:
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-10-31, 9:23 am |
| --=_alternative 0017FF52852570AB_=
Content-Type: text/plain; charset="US-ASCII"
As I suspected, you have no indexes. You didn't even define a primary key
(PK).
For each table, decide which column or combination of columns you can use
to uniquely identify each row. Make that your PRIMARY KEY for each table.
For other columns or combinations of columns you frequently use in your
queries, create INDEXes (KEYs) on them to speed up your query responses.
I suspect that on your `address` table, the `addresss_id` column is unique
for each row. To mark it as the primary key for the `address` table you
could use this command
ALTER TABLE `address` ADD PRIMARY KEY(`address_id`);
The table `address_association
` should probably only contain a single row
for any (address_id, association_id) combination. That would make those
two columns the likely candidate for acting as the PRIMARY KEY for that
table. You would declare such a key as
ALTER TABLE `adress_association`
ADD PRIMARY KEY(`address_id`,
`association_id`);
You should perform the same analysis for all of your other tables. Very
rarely is it ever a good design to allow duplicates of entire rows within
the same table. There should always be something that makes one row unique
from every other row in the same table.
In your case, an additional index on several columns of
`address_association
` will make your particular view much faster.
ALTER TABLE `address_association
` ADD KEY(`owner_class`,`o
wner_id`,
`association_id`);
You need to add all of the appropriate indexes to all of your tables.
Then, look at another EXPLAIN of your SELECT statement, the KEY column
should be filled in for at least 2 of your tables. With the tiny number of
rows you have in your database you should be seeing results returned in
less than 0.05 seconds (even if you have an 1980's machine). It's the
complete lack of indexes that has killed your performance.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"YL" <elim@pdtnetworks.net> wrote on 10/30/2005 10:21:39 PM:
> Thanks Shawn for the help: The same query took 2min less than before on
> 5.0.15 after
> using inner join. Is what you ask:
>
> mysql> show create table address\G;
> ********************
******* 1. row ********************
*******
> Table: address
> Create Table: CREATE TABLE `address` (
> `city` varchar(48) default NULL,
> `country_id` smallint(5) unsigned default NULL,
> `county` varchar(36) default NULL,
> `address_id` int(10) unsigned NOT NULL default '0',
> `status_code` tinyint(4) default NULL,
> `street` text,
> `zip` varchar(12) default NULL,
> `state_id` mediumint(8) unsigned default NULL,
> `zip_ext` varchar(8) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.01 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table address_association\
G;
> ********************
******* 1. row ********************
*******
> Table: address_association
> Create Table: CREATE TABLE `address_association
` (
> `address_id` mediumint(8) unsigned default NULL,
> `association_id` int(10) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `owner_id` mediumint(8) unsigned default NULL,
> `owner_class_name` varchar(32) default NULL,
> `status_code` tinyint(3) unsigned default NULL,
> `flag` varchar(16) default 'default'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table enterprise_unit\G;
> ********************
******* 1. row ********************
*******
> Table: enterprise_unit
> Create Table: CREATE TABLE `enterprise_unit` (
> `name` varchar(80) default NULL,
> `unit_id` mediumint(8) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `parent_id` mediumint(8) unsigned default NULL,
> `status_code` tinyint(4) default NULL,
> `gb_name` varchar(80) default NULL,
> `b5_name` varchar(80) default NULL,
> `path` varchar(80) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> From: <SGreen@unimin.com>
> To: "YL" <elim@pdtnetworks.net>
>
>
>
--=_alternative 0017FF52852570AB_=--
| |
|
| ------ =_NextPart_000_0185_
01C5DDB0.4FBAC530
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks a lot Shawn: After adding index, it's amazingly faster(< 0.15 =
sec, was > 16 min !).=20
I thought index columns would be necessary only for tables have 10,000 =
rows or more but I was so wrong.=20
address_id, association_id and unit_id are PKs for tables Address, =
Address_Association and Enterprise_Unit respectively and I have a table =
eo_pk_table to store the max pk of each table, which is maintained by =
the application to figure out the next key and set the foreign key for =
new records and keep the uniqueness of records. Now tables were modified =
and PKs became explicit at database level. Does this help the =
performance?
association_id is not refered very often, but address_id is. so I =
indexed address_id in address_association.
Is it a good idea to index PKs for big table?
Thanks again,
YL
mysql> show create table address\G;
********************
******* 1. row ********************
*******
Table: address
Create Table: CREATE TABLE `address` (
......
`county` varchar(36) default NULL,
`address_id` int(10) unsigned NOT NULL default '0',
......
`zip_ext` varchar(8) default NULL,
PRIMARY KEY (`address_id`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
mysql> show create table address_association\
G;
********************
******* 1. row ********************
*******
Table: address_association
Create Table: CREATE TABLE `address_association
` (
......
`association_id` int(10) unsigned NOT NULL default '0',
......
PRIMARY KEY (`association_id`),
KEY `owner_class` (`owner_class`,`owne
r_id`,`address_id`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
mysql> show create table enterprise_unit\G;
********************
******* 1. row ********************
*******
Table: enterprise_unit
Create Table: CREATE TABLE `enterprise_unit` (
......
`unit_id` mediumint(8) unsigned NOT NULL default '0',
......
PRIMARY KEY (`unit_id`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
Thanks again
----- Original Message -----=20
From: SGreen@unimin.com=20
To: YL=20
Cc: mysql@lists.mysql.com=20
Sent: Sunday, October 30, 2005 9:25 PM
Subject: Re: 5.0.1 vs 5.0.15: view performance
As I suspected, you have no indexes. You didn't even define a primary =
key (PK).=20
For each table, decide which column or combination of columns you can =
use to uniquely identify each row. Make that your PRIMARY KEY for each =
table. For other columns or combinations of columns you frequently use =
in your queries, create INDEXes (KEYs) on them to speed up your query =
responses.=20
I suspect that on your `address` table, the `addresss_id` column is =
unique for each row. To mark it as the primary key for the `address` =
table you could use this command=20
ALTER TABLE `address` ADD PRIMARY KEY(`address_id`);=2
0
The table `address_association
` should probably only contain a single =
row for any (address_id, association_id) combination. That would make =
those two columns the likely candidate for acting as the PRIMARY KEY for =
that table. You would declare such a key as=20
ALTER TABLE `adress_association`
ADD PRIMARY KEY(`address_id`, =
`association_id`);=2
0
You should perform the same analysis for all of your other tables. =
Very rarely is it ever a good design to allow duplicates of entire rows =
within the same table. There should always be something that makes one =
row unique from every other row in the same table.=20
In your case, an additional index on several columns of =
`address_association
` will make your particular view much faster.=20
ALTER TABLE `address_association
` ADD KEY(`owner_class`,`o
wner_id`, =
`association_id`);=2
0
You need to add all of the appropriate indexes to all of your tables. =
Then, look at another EXPLAIN of your SELECT statement, the KEY column =
should be filled in for at least 2 of your tables. With the tiny number =
of rows you have in your database you should be seeing results returned =
in less than 0.05 seconds (even if you have an 1980's machine). It's the =
complete lack of indexes that has killed your performance.=20
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20
"YL" <elim@pdtnetworks.net> wrote on 10/30/2005 10:21:39 PM:
> Thanks Shawn for the help: The same query took 2min less than before =
on
> 5.0.15 after
> using inner join. Is what you ask:
>=20
> mysql> show create table address\G;
> ********************
******* 1. row ********************
*******
> Table: address
> Create Table: CREATE TABLE `address` (
> `city` varchar(48) default NULL,
> `country_id` smallint(5) unsigned default NULL,
> `county` varchar(36) default NULL,
> `address_id` int(10) unsigned NOT NULL default '0',
> `status_code` tinyint(4) default NULL,
> `street` text,
> `zip` varchar(12) default NULL,
> `state_id` mediumint(8) unsigned default NULL,
> `zip_ext` varchar(8) default NULL
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
> 1 row in set (0.01 sec)
>=20
> ERROR:
> No query specified
>=20
> mysql> show create table address_association\
G;
> ********************
******* 1. row ********************
*******
> Table: address_association
> Create Table: CREATE TABLE `address_association
` (
> `address_id` mediumint(8) unsigned default NULL,
> `association_id` int(10) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `owner_id` mediumint(8) unsigned default NULL,
> `owner_class_name` varchar(32) default NULL,
> `status_code` tinyint(3) unsigned default NULL,
> `flag` varchar(16) default 'default'
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
> 1 row in set (0.00 sec)
>=20
> ERROR:
> No query specified
>=20
> mysql> show create table enterprise_unit\G;
> ********************
******* 1. row ********************
*******
> Table: enterprise_unit
> Create Table: CREATE TABLE `enterprise_unit` (
> `name` varchar(80) default NULL,
> `unit_id` mediumint(8) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `parent_id` mediumint(8) unsigned default NULL,
> `status_code` tinyint(4) default NULL,
> `gb_name` varchar(80) default NULL,
> `b5_name` varchar(80) default NULL,
> `path` varchar(80) default NULL
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
> 1 row in set (0.00 sec)
>=20
> ERROR:
> No query specified
>=20
> From: <SGreen@unimin.com>
> To: "YL" <elim@pdtnetworks.net>
>=20
>=20
no[color=darkred]
result[color=darkred
]
+----+-------------+-------+------+---------------+------+---------[color=darkred]
key_len |[color=darkred]
+----+-------------+-------+------+---------------+------+---------[color=darkred]
|[color=darkred]
|[color=darkred]
|[color=darkred]
+----+-------------+-------+------+---------------+------+---------[color=darkred]
>=20
-------------------------------------------------------------------------=
-----
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: =
10/28/2005
------ =_NextPart_000_0185_
01C5DDB0.4FBAC530--
| |
| Jeremiah Gowdy 2005-10-31, 11:23 am |
| The primary key by definition is an index ("key"). If you don't make it a
primary key index or unique index then duplicate entries can occur. It is
good practice to always set a primary key, and always make it a unique
auto-increment integer.
If you look at the output of EXPLAIN now, you can see your table using the
indexes to perform the query.
----- Original Message -----
From: "YL" <elim@pdtnetworks.net>
To: <SGreen@unimin.com>
Cc: <mysql@lists.mysql.com>
Sent: Sunday, October 30, 2005 11:16 PM
Subject: Re: 5.0.1 vs 5.0.15: view performance
Thanks a lot Shawn: After adding index, it's amazingly faster(< 0.15 sec,
was > 16 min !).
I thought index columns would be necessary only for tables have 10,000 rows
or more but I was so wrong.
address_id, association_id and unit_id are PKs for tables Address,
Address_Association and Enterprise_Unit respectively and I have a table
eo_pk_table to store the max pk of each table, which is maintained by the
application to figure out the next key and set the foreign key for new
records and keep the uniqueness of records. Now tables were modified and PKs
became explicit at database level. Does this help the performance?
association_id is not refered very often, but address_id is. so I indexed
address_id in address_association.
Is it a good idea to index PKs for big table?
Thanks again,
YL
mysql> show create table address\G;
********************
******* 1. row ********************
*******
Table: address
Create Table: CREATE TABLE `address` (
......
`county` varchar(36) default NULL,
`address_id` int(10) unsigned NOT NULL default '0',
......
`zip_ext` varchar(8) default NULL,
PRIMARY KEY (`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> show create table address_association\
G;
********************
******* 1. row ********************
*******
Table: address_association
Create Table: CREATE TABLE `address_association
` (
......
`association_id` int(10) unsigned NOT NULL default '0',
......
PRIMARY KEY (`association_id`),
KEY `owner_class` (`owner_class`,`owne
r_id`,`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> show create table enterprise_unit\G;
********************
******* 1. row ********************
*******
Table: enterprise_unit
Create Table: CREATE TABLE `enterprise_unit` (
......
`unit_id` mediumint(8) unsigned NOT NULL default '0',
......
PRIMARY KEY (`unit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Thanks again
----- Original Message -----
From: SGreen@unimin.com
To: YL
Cc: mysql@lists.mysql.com
Sent: Sunday, October 30, 2005 9:25 PM
Subject: Re: 5.0.1 vs 5.0.15: view performance
As I suspected, you have no indexes. You didn't even define a primary key
(PK).
For each table, decide which column or combination of columns you can use
to uniquely identify each row. Make that your PRIMARY KEY for each table.
For other columns or combinations of columns you frequently use in your
queries, create INDEXes (KEYs) on them to speed up your query responses.
I suspect that on your `address` table, the `addresss_id` column is unique
for each row. To mark it as the primary key for the `address` table you
could use this command
ALTER TABLE `address` ADD PRIMARY KEY(`address_id`);
The table `address_association
` should probably only contain a single row
for any (address_id, association_id) combination. That would make those two
columns the likely candidate for acting as the PRIMARY KEY for that table.
You would declare such a key as
ALTER TABLE `adress_association`
ADD PRIMARY KEY(`address_id`,
`association_id`);
You should perform the same analysis for all of your other tables. Very
rarely is it ever a good design to allow duplicates of entire rows within
the same table. There should always be something that makes one row unique
from every other row in the same table.
In your case, an additional index on several columns of
`address_association
` will make your particular view much faster.
ALTER TABLE `address_association
` ADD KEY(`owner_class`,`o
wner_id`,
`association_id`);
You need to add all of the appropriate indexes to all of your tables.
Then, look at another EXPLAIN of your SELECT statement, the KEY column
should be filled in for at least 2 of your tables. With the tiny number of
rows you have in your database you should be seeing results returned in less
than 0.05 seconds (even if you have an 1980's machine). It's the complete
lack of indexes that has killed your performance.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"YL" <elim@pdtnetworks.net> wrote on 10/30/2005 10:21:39 PM:
> Thanks Shawn for the help: The same query took 2min less than before on
> 5.0.15 after
> using inner join. Is what you ask:
>
> mysql> show create table address\G;
> ********************
******* 1. row ********************
*******
> Table: address
> Create Table: CREATE TABLE `address` (
> `city` varchar(48) default NULL,
> `country_id` smallint(5) unsigned default NULL,
> `county` varchar(36) default NULL,
> `address_id` int(10) unsigned NOT NULL default '0',
> `status_code` tinyint(4) default NULL,
> `street` text,
> `zip` varchar(12) default NULL,
> `state_id` mediumint(8) unsigned default NULL,
> `zip_ext` varchar(8) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.01 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table address_association\
G;
> ********************
******* 1. row ********************
*******
> Table: address_association
> Create Table: CREATE TABLE `address_association
` (
> `address_id` mediumint(8) unsigned default NULL,
> `association_id` int(10) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `owner_id` mediumint(8) unsigned default NULL,
> `owner_class_name` varchar(32) default NULL,
> `status_code` tinyint(3) unsigned default NULL,
> `flag` varchar(16) default 'default'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> mysql> show create table enterprise_unit\G;
> ********************
******* 1. row ********************
*******
> Table: enterprise_unit
> Create Table: CREATE TABLE `enterprise_unit` (
> `name` varchar(80) default NULL,
> `unit_id` mediumint(8) unsigned NOT NULL default '0',
> `property_dict` text,
> `type_id` smallint(5) unsigned default NULL,
> `parent_id` mediumint(8) unsigned default NULL,
> `status_code` tinyint(4) default NULL,
> `gb_name` varchar(80) default NULL,
> `b5_name` varchar(80) default NULL,
> `path` varchar(80) default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> ERROR:
> No query specified
>
> From: <SGreen@unimin.com>
> To: "YL" <elim@pdtnetworks.net>
>
>
>
------------------------------------------------------------------------------
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: 10/28/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|