Switching between queries with "date  now" slows down the performance a lot In our MySQL database we have a table with about 12 million posts. We are using a datetime that is indexed to get posts from it.

There is never any UPDATE, INSERT or DELETE made to the table.
What we do is running three different queries in a row:


SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime &gt;= FROM_UNIXTIME(1479423600)
AND 
datetime &lt;= FROM_UNIXTIME(1479510000)
ORDER BY datetime

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime &gt; FROM_UNIXTIME(1479423600)
LIMIT 1

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime &lt;= FROM_UNIXTIME(1479510000)
LIMIT 1



If we only runt the first two, it works super fast, or if we run one repeatedly after itself, it takes 0.0001 second. But as soon as we search in the opposite direction it gets super slow, because I guess MySQL needs to rebuild the INDEX. Even if we limit the result to 1 post it takes 5 seconds, and we are running this on a dedicated server.

We will get a new server with SSD discs and 64 GB RAM but my colleague says that will not improve the speed enough. What he has suggested is that we create another table and revert the posts there. I would like to find another solution, there must be a way MySQL can store two differently structured indexes for the same table.

Why is the request so slow in some cases?

Answer: The concat slows down everything crazy much!

I simply removed the concat and all queries is super fast now!
You can't get 10 microseconds -- there is just too much overhead in performing a query.  You can get about 1 millisecond.  But you will need INDEX(datetime), at which point, the LIMIT 1 queries will probably run in about a millisecond.  The first query will take an amount of time proportional to the number of rows returned.  If it is a million rows, it may take seconds.

Or perhaps you have the "Query cache" turned on.  It should probably be turned off.  You are probably updating the table a lot; the QC gets flushed for every write.  So 10us will not be consistent, only if no writes happen since you last performed exactly the same query.

You "want the first two..." -- But the answer to the second one is included the first.  And if you add ORDER BY datetime, then it will be the first row.

MySQL "never" needs the index to be rebuilt.

Do not use MyISAM; switch to InnoDB.

One thing that may be happening.  With MyISAM there is only "table locking".  If a lot of writes are coming in, your SELECT could be held up waiting for them.  (Not a problem with InnoDB.)

How many rows in that one day plus one second?

Are those the actual queries?  Or are you limiting to a specific user.  (That would radically change everything I have said.)

If you want to discuss any more, please provide SHOW CREATE TABLE and tell us approximately how many rows in the table.