* SHOW VARIABLES LIKE 'innodb%'; -- some of them may be hurting performance.
* More that 20% of the table has bean_type = '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
>
> true Michael, pasting the output :
>
> 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=InnoDB DEFAULT CHARSET=latin1 |
>
>
> 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
> |
> |
> +---------+------------+------------------------------+--------------+-
> -------------------+-----------+-------------+----------+--------+-----
> -+------------+---------+
>
>
> Thanks
>
> On Wed, Sep 5, 2012 at 8:43 PM, Michael Dykman <mdykman@stripped>
> wrote:
>
> > 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
> >
> >
