Optimizing the Query For selecting N Latest Data From MySQL

Databases
1

To select N latest data obtained from SourceID =‘0000201010107’ from my MySQL database i used the following query:

Query 1)
SELECT DataValue, DateTimeAcquired FROM DataHistoricalValue WHERE SourceID = BINARY ‘0000201010107’ ORDER BY RecordID DESC LIMIT 50 ;

In this case N = 50. Utilizing the above, the query takes 20 second to perform.

If i change to
Query 2)
SELECT DataValue, DateTimeAcquired FROM DataHistoricalValue WHERE SourceID = BINARY ‘0000201010107’ ORDER BY RecordID ASC LIMIT 50 ;

It takes me only 0.003 second to obtain the results. But this gives me the oldes / earliet 50 data. Not what is required ( latest 50 data).

My table has about 30 million rows and i need to usually get the latest data for a given SourceID ( there are multiple SourceID storing data in this table). RecordID is an integer and Primary Index and SourceID is also an Index.

I tried to set RecordID index to DESC type as apposed to the default ASC type but still with no luck.
Can anyone point to the correction direction to improve Query 1 performance?

TIA

2

MySQL does not use indexes for descending order by default: https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

Using BINARY might cause the optimizer not to use the index. If SourceID is already a binary string in the database, you should not need to use BINARY in your WHERE clause.

Instead of ordering by RecordID DESC, which forces a full index scan, you can try to select the top 50 RecordIDs in a subquery and then join this result back to your original table to get the corresponding data.

SELECT DataValue, DateTimeAcquired
FROM DataHistoricalValue
WHERE RecordID IN (
SELECT RecordID
FROM DataHistoricalValue
WHERE SourceID = ‘0000201010107’
ORDER BY RecordID DESC
LIMIT 50
)
ORDER BY RecordID DESC;

Also, have you changed the default memory allocation, which is by default 512MB?

1 Like
3

Thanks Daniel

  1. The internal query
    SELECT RecordID
    FROM DataHistoricalValue
    WHERE SourceID = ‘0000201010107’
    ORDER BY RecordID DESC
    LIMIT 50

Itself takes 12 second to complete

  1. My system’s default innodb_buffer_pool_size = 128
    I tried changing to 512 but i see no differences in speed ( My machine has 8GB of RAM)’

I am using BINARY coz the string are case sensitive.
But i am a bit curious about DESC indexes.
What are the use case scenario then?
How do people with large data rows select latest N data from their MySQL database in the fastest time possible?

4

This is what my.ini has, Production, with only about 6gb of data, many millions of records.

innodb_buffer_pool_size=8589934592

I suggest trying with the following value (or even double) for 2gb or (4gb)

innodb_buffer_pool_size=2147483648

5

Will try and update soonest.
Thanks Daniel

6

I have managed to solve this problem. Moved my tables from MySQL to PostgreSQL and with some tuning at the database server side ( no changes at XOJO code side, except for change of plugin from MySQL to PostgreSQL). Some queries were down from 40-50++ seconds to less than 500 ms even.

1 Like