* 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.ada...@gmail.com] > Sent: Wednesday, September 05, 2012 11:27 AM > To: Michael Dykman > Cc: mysql@lists.mysql.com > 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 <mdyk...@gmail.com> > 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.ada...@gmail.com> 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 <man...@tuenti.com> > > wrote: > > > > > > > > > > > > 2012/9/5 Adar... > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
