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
