Prism

Prism

91.2k Downloads

Combine purge min/max ID queries into one

Bobcat00 opened this issue ยท 7 comments

commented

Instead of doing:

SELECT min(id) FROM prism_data  WHERE ...
SELECT max(id) FROM prism_data  WHERE ...

minId and maxId can be determined by a single query:

SELECT MIN(id) AS minid, MAX(id) AS maxid, COUNT(*) AS rows FROM prism_data WHERE ...

This should be faster than two separate queries, although that should be confirmed with timing measurements before this is implemented.

commented

I think the COUNT part should be eliminated, unless there's some purpose to doing it. So the new query would be:

SELECT MIN(id) AS minid, MAX(id) AS maxid FROM prism_data WHERE ...

And a quick test with phpMyAdmin has the timing being about the same as each of the two queries being done now.

commented

Yes, these queries can (and should) be combined. The COUNT(*) is harmless (since a full table scan is required to determine Max and Min), but if it's not being used, should be removed.

commented

Can a SQL guy confirm if this is possible and how much performance it would gain?

commented

Yes, these queries can (and should) be combined. The COUNT(*) is harmless (since a full table scan is required to determine Max and Min), but if it's not being used, should be removed.

Agreed.

Can a SQL guy confirm if this is possible and how much performance it would gain?

Doing two separate queries for MIN() and MAX() is a waste of resources (and bloats the code) because they both have to scan the same set of rows, based on the WHERE clause. So doing them in a single query would be more efficient. It's probably not a huge improvement because this part of the process is usually relatively fast compared to the rest of the work, but it's the "right" thing to do unless the code changes to do so is significant.

commented

Well, it takes half the time. And with a complicated purge rule, that could be several seconds saved.

commented

I searched the source code last week for a place where Min() and then Max() were looked up sequentially and I couldn't find the method (or subsequent method calls). Can somebody point us to some actual code?