MySQL Forums
Forum List  »  InnoDB

Different "Fetch Time" for Same Query with Diff Indexes
Posted by: Deepak Gupta
Date: March 18, 2018 04:04PM

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,,, metric_value from collections_history_table join t_attribute ON = fkid_attribute join t_attribute_grp ON = fkid_attribute_grp_name join t_audit_grp ON = fkid_audit_grp_name where 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,
`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`),

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.


Options: ReplyQuote

Written By
Different "Fetch Time" for Same Query with Diff Indexes
March 18, 2018 04:04PM

Sorry, only registered users may post in this forum.

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.