Different "Fetch Time" for Same Query with Diff Indexes
Hi There
I an using MySQL 5.7.20 with my schema on InnoDB engine.
My schema four tables:
1. "Collections_history" with approx 60 Million Records
2. "Attribute" with 6 records
3. "Attribute_grp" with 6 records
4. "Audit_Grp" with 6 records.
The "collections_history" table contains time series data that apart from timestamp and an integer measured value contains three foreign keys referring back to rest of the three tables.
Now I want to run the following query in the most efficient way:
select t_attribute.name, t_attribute_grp.name, t_audit_grp.name, metric_value from collections_history_table join t_attribute ON t_attribute.id = fkid_attribute join t_attribute_grp ON t_attribute_grp.id = fkid_attribute_grp_name join t_audit_grp ON t_audit_grp.id = fkid_audit_grp_name where t_attribute.name like "%CPU Usage%";
Here is the "Create" query for the collections_history table:
CREATE TABLE `collections_history_table` (
`idcollections_history_table` int(11) NOT NULL AUTO_INCREMENT,
`metric_value` int(11) NOT NULL,
`date_insert` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`fkid_audit_grp_name` int(11) NOT NULL,
`fkid_attribute_grp_name` int(11) NOT NULL,
`fkid_attribute` int(11) NOT NULL,
PRIMARY KEY (`idcollections_history_table`),
UNIQUE KEY `idcollections_history_table_UNIQUE` (`idcollections_history_table`),
) ENGINE=InnoDB AUTO_INCREMENT=141352835 DEFAULT CHARSET=latin1;
On my 2.2 Ghz Quad core machine with ~approx 7G given to MySQL instance I get a followings stats:
- "Query Duration" ranges from 0.003 to 0.03 Secs
- "Query Fetch Time" ranges from 35 to 60 Secs
In my attempt to optimize performance I added three additional indexes to the "collections_history" table:
KEY `index3` (`fkid_audit_grp_name`),
KEY `index4` (`fkid_attribute_grp_name`),
KEY `index5` (`fkid_attribute`)
The results post the change I got were as follows:
- "Query Duration" ranges from 0.005 to 0.02 Secs
- "Query Fetch Time" ranges from 740 to 1000 Secs
So as you see that there was no significant difference in the Query execution time but there was a 20X increase in the "Fetch Time". Why is this so?
Few other items:
1. The total rows/Columns returned in all cases (pre/post) was identical at ~10 Million Records
2. Inno_db_buffer_pool_size in both case was set to be 2GB
Any guidance is appreciated.
Regards
Deepak
Subject
Views
Written By
Posted
Different "Fetch Time" for Same Query with Diff Indexes
761
March 18, 2018 04:04PM
520
March 18, 2018 06:32PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.