https://shlomi-noach.github.io/blog/mysql/monotonic-functions-sql-and-mysql Blog by Shlomi Noach Tue, 15 Nov 2016 05:48:00 +0000 hourly 1 https://wordpress.org/?v=5.3.3 https://shlomi-noach.github.io/blog/mysql/monotonic-functions-sql-and-mysql/comment-page-1#comment-378380 Tue, 15 Nov 2016 05:48:00 +0000 https://shlomi-noach.github.io/blog/?p=1908#comment-378380 @sactiw:disqus yes indeed. This isn’t an optimal solution, for sure. The ideal is to just have an index, if your use case is to find rows based on timestamp (as in the above example). However if index won’t/cannot be created, this beats full table scan.

]]> https://shlomi-noach.github.io/blog/mysql/monotonic-functions-sql-and-mysql/comment-page-1#comment-378356 Mon, 14 Nov 2016 17:24:00 +0000 https://shlomi-noach.github.io/blog/?p=1908#comment-378356 Hi Shlomi,
Can you elaborate this further?
” If we were to look for a log entry from ‘2009-02-07 11:58:00’ by simple SELECT, we would have to use a full table scan. But, by knowing that ts is monotonic, we can also use binary search on id.”
So are you suggesting we take a count(*) AS c and then get MAX(id) as r, MAX(ts) as t from table order by id limit c and then use r and t to continue the binary search until we locate the exact time stamp in t?
If yes, won’t that means writing lot of logic in application layer and also making more than 1 query to underlying db?

]]> https://shlomi-noach.github.io/blog/mysql/monotonic-functions-sql-and-mysql/comment-page-1#comment-18426 Mon, 04 Oct 2010 08:07:55 +0000 https://shlomi-noach.github.io/blog/?p=1908#comment-18426 […] in between, and ran for about a couple of hours. It may be interesting to note that since ts is in monotonically ascending values, purging of old rows also means purging of lower PKs, which means we’re trimming the […]

]]>