TOP [N] Records Group By per user query in the best possible way

Question

I faced an interesting problem today:

Lets suppose the following conditions

1. There are n number of users

2. The system collects GPS coordinates of each driver as they move

3. We have to query last 10 GPS Coordinate records per user sorted by LAST_UPDATE_DATE in descending order

4. There are over 1982008 records in the table

I solved this by querying the last {(10 + [threshold value]) * n} records, and arranged them in java using a HashMap where the user is Key and List of respective coordinates are values

This doesn't seem to be a best possible solution as it may choose random records and may not cover up 10 records per user,

Querying it per user in a for loop is also not a best solution as it would need multiple database calls

Can anybody suggest what would be a best possible solution for this using Spring data JPA, I am also open to use Normal JDBC in case Spring data JPA is unable to solve this

I am using Spring data JPA, Mysql, Java 8 for this

Here is the create table command for this

@RaymondNijland Ok sure, here is the create table command

CREATE TABLE `gps_coordinate` (
    `ID` BIGINT(50) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR(255) NULL DEFAULT '0',
    `driver_id` INT(20) NULL DEFAULT '0',
    `latitude` VARCHAR(50) NULL DEFAULT '0.00000000',
    `longitude` VARCHAR(50) NULL DEFAULT '0.00000000',
    `distance_in_miles` VARCHAR(50) NULL DEFAULT '0.00',
    `distance_in_kms` VARCHAR(50) NULL DEFAULT '0.00',
    `device_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by` VARCHAR(50) NULL DEFAULT 'Anonymous',
    `created_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(50) NULL DEFAULT 'Anonymous',
    `updated_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`ID_HUB`),
    INDEX `user_fk` (`user_id`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=208445
;

Answer 1

The best solution generally is to leave the sorting and extraction to the database. If you need to sort and extract data locally in java you need to extract all the data from the database to your java server. This generate a huge overhead in network traffic, generating poor performances.

There are many databases that handle very well geo spatial data and can query your tables offering sorting by distance or similar. If you need to get only the last 10 positions per user you don't need geo spatial features and any database work well.

For a solution in MySql read this article.

For a geo database you can also use MySql. Here a link to the spatial data types supported by MySql.