From: Rick James
Date: September 5 2012 9:02pm
Subject: RE: Understanding Slow Query Log
List-Archive: http://lists.mysql.com/mysql/228119
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148B88941B@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

* SHOW VARIABLES LIKE 'innodb%';  -- some of them may be hurting performanc=
e.

* More that 20% of the table has bean_type =3D 'Workflow'?  -- if so, it is=
 more efficient to do a table scan than to use the index.

* KEY `I_WF_1_DTYPE` (`bean_type`), -->
KEY bean_time (`bean_type`, created_time)
"Compound" index may be your cure.

* Fields with low cardinality (bean_type, status) make very poor INDEXes.

* Consider using an ENUM instead of VARCHAR for status and bean_type, (and =
others?)

* VARCHAR(255) is an awful PRIMARY KEY.  The PK is included implicitly (in =
InnoDB) in every secondary key.

* LIMIT 0, 50 -- are you doing "pagination" via OFFSET?  Bad idea.

Lots more about these topics is discussed in similar questions in
http://forums.mysql.com/list.php?24
Lots more tips here:
http://mysql.rjweb.org/doc.php/ricksrots


> -----Original Message-----
> From: Adarsh Sharma [mailto:eddy.adarsh@stripped]
> Sent: Wednesday, September 05, 2012 11:27 AM
> To: Michael Dykman
> Cc: mysql@stripped
> Subject: Re: Understanding Slow Query Log
>=20
> true Michael, pasting the output :
>=20
> CREATE TABLE `WF_1` (
>   `id` varchar(255) NOT NULL,
>   `app_name` varchar(255) DEFAULT NULL,
>   `app_path` varchar(255) DEFAULT NULL,
>   `conf` text,
>   `group_name` varchar(255) DEFAULT NULL,
>   `parent_id` varchar(255) DEFAULT NULL,
>   `run` int(11) DEFAULT NULL,
>   `user_name` varchar(255) DEFAULT NULL,
>   `bean_type` varchar(31) DEFAULT NULL,
>   `auth_token` text,
>   `created_time` datetime DEFAULT NULL,
>   `end_time` datetime DEFAULT NULL,
>   `external_id` varchar(255) DEFAULT NULL,
>   `last_modified_time` datetime DEFAULT NULL,
>   `log_token` varchar(255) DEFAULT NULL,
>   `proto_action_conf` text,
>   `sla_xml` text,
>   `start_time` datetime DEFAULT NULL,
>   `status` varchar(255) DEFAULT NULL,
>   `wf_instance` mediumblob,
>   PRIMARY KEY (`id`),
>   KEY `I_WF_1_DTYPE` (`bean_type`),
>   KEY `I_WF_1_END_TIME` (`end_time`),
>   KEY `I_WF_1_EXTERNAL_ID` (`external_id`),
>   KEY `I_WF_1_LAST_MODIFIED_TIME` (`last_modified_time`),
>   KEY `I_WF_1_STATUS` (`status`)
> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 |
>=20
>=20
> show indexes from WF_1;
> +---------+------------+------------------------------+--------------+-
> -------------------+-----------+-------------+----------+--------+-----
> -+------------+---------+
> | Table   | Non_unique | Key_name                     | Seq_in_index |
> Column_name        | Collation | Cardinality | Sub_part | Packed | Null
> |
> Index_type | Comment |
> +---------+------------+------------------------------+--------------+-
> -------------------+-----------+-------------+----------+--------+-----
> -+------------+---------+
> |WF_1 |          0 | PRIMARY                      |            1 | id
>           | A         |      551664 |     NULL | NULL   |      | BTREE
>  |         |
> |WF_1 |          1 | I_WF_1_DTYPE              |            1 |
> bean_type
>        | A         |          18 |     NULL | NULL   | YES  | BTREE
> |
>         |
> |WF_1 |          1 | I_WF_1_END_TIME           |            1 |
> end_time
>         | A         |      551664 |     NULL | NULL   | YES  | BTREE
> |
>         |
> |WF_1 |          1 | I_WF_1_EXTERNAL_ID        |            1 |
> external_id
>        | A         |      551664 |     NULL | NULL   | YES  | BTREE
> |
>         |
> |WF_1 |          1 | I_WF_1_LAST_MODIFIED_TIME |            1 |
> last_modified_time | A         |      551664 |     NULL | NULL   | YES
> |
> BTREE      |         |
> |WF_1 |          1 | I_WF_1_STATUS             |            1 | status
>         | A         |          18 |     NULL | NULL   | YES  | BTREE
> |
>         |
> +---------+------------+------------------------------+--------------+-
> -------------------+-----------+-------------+----------+--------+-----
> -+------------+---------+
>=20
>=20
> Thanks
>=20
> On Wed, Sep 5, 2012 at 8:43 PM, Michael Dykman <mdykman@stripped>
> wrote:
>=20
> > The attachments do not appear to be coming through.
> >
> > I am more curious what the cardinality of bean_type is.  What is the
> > result of select count(*) as cnt, bean_type from WS_1 group by
> > bean_type  ?
> >
> > Low cardinality can render an index usrless.
> >
> > On 2012-09-05 5:19 AM, "Adarsh Sharma" <eddy.adarsh@stripped> wrote:
> >
> > I already attached the list.
> >
> > Attaching one more time & thanks for the interest.
> >
> > Cheers
> >
> >
> >
> > On Wed, Sep 5, 2012 at 11:44 AM, Manuel Arostegui <manuel@stripped>
> > wrote:
> > >
> > >
> > >
> > > 2012/9/5 Adar...
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
> >
> >