Adarsh Sharma Are Single Column Indexes are sufficient
Sep 18, 2012; 10:36
Adarsh Sharma
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';
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
Sep 18
Rick James RE: Are Single Column Indexes are sufficient
Sep 18, 2012; 08:59
Rick James
RE: Are Single Column Indexes are sufficient
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!