List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:September 18 2012 5:06am
Subject:Are Single Column Indexes are sufficient
View as plain text  
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