Require help in fine tuning MySQL 5.5 query
Posted by:
Kushal M
Date: June 22, 2020 01:04AM
Hello All,
I am trying to retrieve data from MySQL 5.5 server DB containing more than 200 Million record. Currently my query is taking more than 4 minutes to retrieve the information from the table.
Below is my table details
Identifier | Host_PODS | Epoch_time | Metric_ID | Metric_Value |DiskSpace_Info
sting1 | string1 | Datetime1 | int1 | double float1| sting1/null
sting2 | string2 | Datetime2 | int2 | double float2| sting2/null
sting3 | string3 | Datetime3 | int3 | double float3| sting3/null
. . . . . .
. . . . . .
. . . . . .
. . . . . .
stingn | stringn | Datetimen | intn | double floatn| stingn/null
My primary_key(indexes) are Identifier, Host_PODS, Epoch_time and Metric_ID
Below is my table structure
+----------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------------------+-------+
| Identifier | varchar(100) | NO | PRI | | |
| Host_PODS | varchar(50) | NO | PRI | | |
| Epoch_time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| Metric_ID | int(11) | NO | PRI | 0 | |
| Metric_Value | double | YES | | NULL | |
| DiskSpace_Info | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------------------+-------+
And below is the query I am currently using to retrieve the data
SELECT Identifier, Host_PODS, Epoch_time, Metric_ID, AVG(Metric_Value), DiskSpace_Info FROM CP_DataPoints
WHERE Identifier LIKE '%12342586%' AND Identifier LIKE '%ABC_APP%' AND
(Epoch_time BETWEEN '2020-01-01 00:00:00' AND '2020-01-02 00:00:00')
GROUP BY Identifier, Host_PODS, Epoch_time, Metric_ID;
Can you please help me with modifying the query to improve the performance.
Thanks,
Kushal M
Subject
Written By
Posted
Require help in fine tuning MySQL 5.5 query
June 22, 2020 01:04AM
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.