MySQL Forums
Forum List  »  General

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

Options: ReplyQuote


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.