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.ada...@gmail.com]
> Sent: Monday, September 17, 2012 10:06 PM
> To: mysql@lists.mysql.com
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
