MySQL - need help in improving query performance

Original question was based on where best to set tx isolation to READ UNCOMMITTED but after some advise it would seem that my initial thoughts on that as a possible solution was incorrect.

DDL

CREATE TABLE `tblgpslog` (
    `GPSLogID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `DTSaved` DATETIME NULL DEFAULT NULL,
    `PrimaryAssetID` BIGINT(20) NULL DEFAULT NULL,
    `SecondaryAssetID` BIGINT(20) NULL DEFAULT NULL,
    `ThirdAssetID` BIGINT(20) NULL DEFAULT NULL,
    `JourneyType` CHAR(1) NOT NULL DEFAULT 'B',
    `DateStamp` DATETIME NULL DEFAULT NULL,
    `Status` VARCHAR(50) NULL DEFAULT NULL,
    `Location` VARCHAR(255) NULL DEFAULT '',
    `Latitude` DECIMAL(11,8) NULL DEFAULT NULL,
    `Longitude` DECIMAL(11,8) NULL DEFAULT NULL,
    `GPSFix` CHAR(2) NULL DEFAULT NULL,
    `Speed` BIGINT(20) NULL DEFAULT NULL,
    `Heading` INT(11) NULL DEFAULT NULL,
    `LifeOdometer` BIGINT(20) NULL DEFAULT NULL,
    `Extra` VARCHAR(20) NULL DEFAULT NULL,
    `BatteryLevel` VARCHAR(5) NULL DEFAULT '--',
    `Ignition` TINYINT(4) NOT NULL DEFAULT '1',
    `Radius` INT(11) NOT NULL DEFAULT '0',
    `GSMLatitude` DECIMAL(11,8) NOT NULL DEFAULT '0.00000000',
    `GSMLongitude` DECIMAL(11,8) NOT NULL DEFAULT '0.00000000',
    PRIMARY KEY (`GPSLogID`),
    UNIQUE INDEX `GPSLogID` (`GPSLogID`),
    INDEX `SecondaryUnitID` (`SecondaryAssetID`),
    INDEX `ThirdUnitID` (`ThirdAssetID`),
    INDEX `DateStamp` (`DateStamp`),
    INDEX `PrimaryUnitIDDateStamp` (`PrimaryAssetID`, `DateStamp`, `Status`),
    INDEX `Location` (`Location`),
    INDEX `DTSaved` (`DTSaved`),
    INDEX `PrimaryAssetID` (`PrimaryAssetID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=153076364
;

The original query is as follows

SELECT L.GPSLogID, L.DateStamp, L.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel, Ignition, L.Extra 
FROM tblGPSLog L 
WHERE PrimaryAssetID = 183 AND L.GPSLogID > 147694199
ORDER BY DateStamp ASC 
LIMIT 100;

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","L","index_merge","PRIMARY,GPSLogID,PrimaryUnitIDDateStamp,PrimaryAssetID","PrimaryAssetID,PRIMARY","9,8",\N,"96","Using intersect(PrimaryAssetID,PRIMARY); Using where; Using filesort"

This gave issues a few months ago and after a bit of investigation I changed the query to below, but that is now acting very similar.

EXPLAIN SELECT GPSLogID, DateStamp, tmpA.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel, Ignition, tmpA.Extra, 
PrimaryAssetID FROM (SELECT L.GPSLogID, L.DateStamp, L.Status, Location, Latitude, Longitude, GPSFix, Speed, Heading, LifeOdometer, 
BatteryLevel, Ignition, L.Extra, PrimaryAssetID 
FROM tblGPSLog L 
WHERE L.GPSLogID > 147694199) AS tmpA 
WHERE PrimaryAssetID = 183 
ORDER BY DateStamp ASC;

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","PRIMARY","<derived2>","ALL",\N,\N,\N,\N,"5380842","Using where; Using filesort"
"2","DERIVED","L","range","PRIMARY,GPSLogID","PRIMARY","8",\N,"8579290","Using where"

Thanks for any advise.

Jim

Solution

Your statements

its rare that an SELECT would hit the table while INSERT is happening and even if it does, it wouldn't cause any great issues. DELETE statements are scheduled once a week only at off peak hours,

equate to "Changing the isolation mode won't help much."

I recommend setting long_query_time=1 and turning on the slowlog. Later, look through the slowlog with pt-query-digest to find the few "worst" queries. Then let's discuss improving them.

More

INDEX `PrimaryUnitIDDateStamp` (`PrimaryAssetID`, `DateStamp`,
INDEX `PrimaryAssetID`         (`PrimaryAssetID`)

The first of those takes care of the second, so the second is unnecessary.

PRIMARY KEY             (`GPSLogID`),
UNIQUE INDEX `GPSLogID` (`GPSLogID`),

A PK is a UNIQUE key, so chuck the second of those. That extra unique index slows down inserts and wastes disk space.

In this, I see no reason to have a query and subquery:

SELECT  GPSLogID, DateStamp, tmpA.Status, Location, Latitude,
        Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel,
        Ignition, tmpA.Extra, PrimaryAssetID
    FROM  
        ( SELECT  L.GPSLogID, L.DateStamp, L.Status, Location, Latitude,
                  Longitude, GPSFix, Speed, Heading, LifeOdometer, BatteryLevel,
                  Ignition, L.Extra, PrimaryAssetID
            FROM  tblGPSLog L
            WHERE  L.GPSLogID > 147694199
        ) AS tmpA
    WHERE  PrimaryAssetID = 183
    ORDER BY  DateStamp ASC;

A pair of DECIMAL(11,8) adds up to 12 bytes, and is overkill for lat&lng. See this for smaller alternatives.

The table has been growing in size, correct? And, after it got so big, performance took a nose dive? Shrinking datatypes to shrink the table is one approach, albeit a temporary fix.

Using intersect(PrimaryAssetID,PRIMARY) -- Almost always, it is better to build a composite index than to use "Index merge intersect".

Although

INDEX `PrimaryAssetID`         (`PrimaryAssetID`)

should have been equivalent to

INDEX `PrimaryAssetID`         (`PrimaryAssetID`, GPSLogID)

something is preventing it. Suggest you add this 2-column composite index. Perhaps a large percentage of rows have PrimaryAssetID = 183?? If convenient, please do SELECT COUNT(*) FROM tblgpslog WHERE PrimaryAssetID = 183

Will you be purging 'old' data from this log? If so, the optimal way involves PARTITIONing; see this.