rev107, timeline is causing full table scans

[alager]

Upgraded from 104 to version 107: Now the timeline is excruciatingly slow. Looking at mysql performance schema, it shows that the query below is doing full table scans.

Query, Full Table Scan, Executed (#), Errors (#), Warnings (#), Total Time, Max Time, Avg Time, Rows Sent (#), Avg. Rows Sent (#), Rows Scanned (#), Avg. Rows Scanned (#), Temp. Tables (#), Temp. Disk Tables (#), Rows Sorted (#), Sort Merge Passes (#), Digest
SELECT FrameId , Score FROM Frames WHERE EventId = ? AND Score > ? ORDER BY Score DESC LIMIT ?
 , *, 5685, 0, 0, 285130676.05, 404716.10, 50154.91, 5685, 1.0, 1225681864, 215599.0, 0, 0, 5685, 0, 91e3eb7bb6a6bf4c8cd5adf43adb26df

[kylejohnson]

@alager Thanks for the report.

This is probably a result of #1075, which changed the primary key of Frames and as a result dropped the existing composite (FrameId, EventId) primary key.

Fix might be to create a FrameID, EventID Index?

[Linwood-F]

Having two indexes is going to substantially increase insert time, as well as cleanups' delete time. Perhaps not a big deal with bulk record types, but it may be worth trying to quantify.

[kylejohnson]

@Linwood-F Thanks. I'll have to do some research and testing before we get a fix merged in (unless someone wants to handle that for me)

[alager]

I added a single index on EventId and now the timeline pops up super speedy again.
So far I don't see any appreciable performance hit on dealing with an additional index, but I'm not sure of all the places to check/watch.

[alager]

I thought I'd check changing the index to the Score column, due to it being in both the where clause and the sort clause, but it made only a minor improvement in bringing up the timeline.
Finally moved the index back to the EventId column, and speed has returned!

[kylejohnson]

@alager Which command did you use to create the index, exactly? We'd like to get it into the next release.

[alager]

ALTER TABLE `zm`.`Frames` 
ADD INDEX `EventId_idx` (`EventId` ASC);