Combine purge min/max ID queries into one
Bobcat00 opened this issue ยท 7 comments
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.
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.
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.
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.
Well, it takes half the time. And with a complicated purge rule, that could be several seconds saved.
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?