List:General Discussion« Previous MessageNext Message »
From:YL Date:October 31 2005 3:21am
Subject:Re: 5.0.1 vs 5.0.15: view performance
View as plain text  
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@stripped>
To: "YL" <elim@stripped>


> 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>

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