[Solved] MYSQL: Possible to select the nearest timestamp relative to X which is > than X. If none, then result should be the nearest that is < X?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

MYSQL: Possible to select the nearest timestamp relative to X which is > than X. If none, then result should be the nearest that is < X?

Database type:

I have a constant timestamp of say 18040986 (in my query its $readtime).

Now my table has many entries and each entry has its own timestamp.

I wanted to efficiently search to find the timestamp which is greater then the constant timestamp, but is also the nearest to that. (In essence I am trying to find the first unread entry by a user)

Now the trick is, that say there have been no entries made then I would love to get the nearest timestamp that is less then the current timestamp (ie. the nearest old entry that has already been read)

I was hoping for help with the query to achieve that. The basic queries I have made so far have either been highly inefficient and have slowed my server to a crawl or have been inaccurate. The query I am using for now is this 

                    SELECT postid 
                    FROM theposts
                    WHERE category = '$tid' AND visible='1' AND dateline > '$readtime' 
                    LIMIT 1

But I am worried that the above query wont actually return the next nearest postid that is greater then my readtime if for some reason my table has entries out of order one day

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Use Numeric Column Types For Numeric Values (query line: 7): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
ALTER TABLE `theposts` ADD INDEX `theposts_idx_category_visible_dateline` (`category`,`visible`,`dateline`);
The optimized query:
SELECT
        theposts.postid 
    FROM
        theposts 
    WHERE
        theposts.category = '$tid' 
        AND theposts.visible = '1' 
        AND theposts.dateline > '$readtime' LIMIT 1

Related Articles



* original question posted on StackOverflow here.