List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 18 2012 3:59pm
Subject:RE: Are Single Column Indexes are sufficient
View as plain text  
    WHERE  (t0.job_id = '0000006-120613043532587-o-C')
      AND  t0.bean_type = 'ActionItems';
Begs for 
  INDEX(job_id, bean_type) -- in either order
----
    where  job_id = '0043189-120805203721153-o-C'
      and  nominal_time >= '2012-09-07 07:16:00'
      and  nominal_time < '2012-09-07 08:06:00'
    group by  status;
Begs for
  INDEX(job_id, normal_time) -- in THIS order
It cannot make effective use of `status` in an index.
----
    WHERE  (t0.pending > 0
      AND  (t0.status = 'SUSPENDED'
       OR  t0.status = 'KILLED'
       OR  t0.status = 'RUNNING')
      AND  t0.last_modified_time <= '2012-09-07 08:08:34')
      AND  t0.bean_type = 'ActionItems';
Change the `status` check to
AND t0.status IN ('SUSPENDED', 'KILLED', 'RUNNING')
Other compound indexes might work, but I guess this is the best:
  INDEX(bean_type, status, last_modified_time)  -- in THIS order
----
Note that I put the '=' field(s) first in the INDEX.
Then I put _one_ range field next.  ("IN" is sort of in between "=" and "range".)

Single-field indexes _might_ use the "index merge" feature -- but rarely.  And almost
always an appropriate "compound" index will out-perform it.

Usually I ignore "cardinality" when picking an index.

A single-field index on a 'flag' or low cardinality field is almost never chosen by the
optimizer.  Don't bother having such indexes.

When asking performance questions, please provide
SHOW CREATE TABLE (not DESCRIBE)
SHOW TABLE STATUS (for size info)
EXPLAIN SELECT ...

To figure out how many fields of the index are being used, look at the "len" field of
EXPLAIN, then look at the sizes of the fields in the chosen index.  (Add 1 for NULLable
fields.)

> -----Original Message-----
> From: Adarsh Sharma [mailto:eddy.adarsh@stripped]
> Sent: Monday, September 17, 2012 10:06 PM
> To: mysql@stripped
> Subject: Are Single Column Indexes are sufficient
> 
> Hi all,
> 
> Currently i am doing performance level tuning of some queries that are
> running very slow in my slow -query log. Below are the sample of some
> queries & the cardinality of indexes :-
> --- Below queries take more than 15 minutes to complete on a table
> scd_table of size 7 GB SELECT t0.id, t0.bean_type, t0.action_number,
> t0.action_xml, t0.console_url, t0.created_conf, t0.error_code,
> t0.error_message, t0.external_status, t0.missing_dependencies,
> t0.run_conf, t0.time_out, t0.tracker_uri, t0.job_type, t0.created_time,
> t0.external_id, t0.job_id, t0.last_modified_time, t0.nominal_time,
> t0.pending, t0.rerun_time, t0.sla_xml, t0.status FROM scd_table t0
> WHERE (t0.job_id =
> '0000006-120613043532587-o-C') AND t0.bean_type = 'ActionItems';
> 
> select status, count(*) as cnt from scd_table where job_id = '0043189-
> 120805203721153-o-C' and nominal_time >= '2012-09-07 07:16:00' and
> nominal_time < '2012-09-07 08:06:00' group by status;
> 
> SELECT t0.id, t0.bean_type, t0.action_number, t0.action_xml,
> t0.console_url, t0.created_conf, t0.error_code, t0.error_message,
> t0.external_status, t0.missing_dependencies, t0.run_conf, t0.time_out,
> t0.tracker_uri, t0.job_type, t0.created_time, t0.external_id,
> t0.job_id, t0.last_modified_time, t0.nominal_time, t0.pending,
> t0.rerun_time, t0.sla_xml, t0.status FROM scd_table t0 WHERE
> (t0.pending > 0 AND (t0.status = 'SUSPENDED' OR t0.status = 'KILLED' OR
> t0.status = 'RUNNING') AND t0.last_modified_time <= '2012-09-07
> 08:08:34') AND t0.bean_type = 'ActionItems';
> 
> mysql> show indexes from scd_table;
> +---------------+------------+------------------------------+----------
> ----+--------------------+-----------+-------------+----------+--------
> +------+------------+---------+
> | Table         | Non_unique | Key_name                     |
> Seq_in_index
> | Column_name        | Collation | Cardinality | Sub_part | Packed |
> Null |
> Index_type | Comment |
> +---------------+------------+------------------------------+----------
> ----+--------------------+-----------+-------------+----------+--------
> +------+------------+---------+
> | scd_table |          0 | PRIMARY                      |            1
> | id
>                 | A         |      188908 |     NULL | NULL   |      |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_CREATED_TIME       |            1
> |
> created_time       | A         |      188908 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_DTYPE              |            1
> |
> bean_type          | A         |          14 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_EXTERNAL_ID        |            1
> |
> external_id        | A         |      188908 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_JOB_ID             |            1
> |
> job_id             | A         |         365 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_LAST_MODIFIED_TIME |            1
> |
> last_modified_time | A         |      188908 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_RERUN_TIME         |            1
> |
> rerun_time         | A         |          14 |     NULL | NULL   | YES
> |
> BTREE      |         |
> | scd_table |          1 | I_CRD_TNS_STATUS             |            1
> |
> status             | A         |          14 |     NULL | NULL   | YES
> |
> BTREE      |         |
> +---------------+------------+------------------------------+----------
> ----+--------------------+-----------+-------------+----------+--------
> +------+------------+---------+
> 
> Whenever i explain the query it takes the index with low cardinality.
> Can I remove all the indexes and create only 1-2 multi column index or
> any other tuning that i can do for the above queries. Please let me
> know if any other info is reqd. ( table schema has the same columns
> mentioned in select clause ).
> 
> 
> Thanks
Thread
Are Single Column Indexes are sufficientAdarsh Sharma18 Sep
  • RE: Are Single Column Indexes are sufficientRick James18 Sep