Hello everyone, thanks in advance for your help.
Basically I have a table for articles similar to this one:
CREATE TABLE `articles` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`idAutor` int(10) NOT NULL,
`idUser` int(10) NOT NULL,
`body` text,
`order` int(4) NOT NULL DEFAULT '0',
`state` enum('ON','OFF','DELETED') NOT NULL DEFAULT 'desactivado',
PRIMARY KEY (`id`),
KEY `indx_main_order` (`date`,`order`,`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
When a new article is inserted, the date/time can be set into the future as not to be seen on the website until it should. I have acomplished this with the following query:
SELECT
*
FROM
articles AS art
WHERE
art.state = 'ON'
AND
art.date <= NOW()
ORDER BY
art.date DESC,
art.order DESC,
art.id DESC
LIMIT
100
Even though is not THAT slow, I want to reduce the number of rows being analized by the query optimizer so when I do joins things won't start getting slower. So far this is what an explain returns:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | art | range| inx_fecha | inx_fecha | 4 | _NULL_ | 2628 | Using where |
I have thought of inserting future articles in an OFF state and then running a cron-job that will update them to ON when the time is right, but I really wouldn't want to resort to that.
Has anyone gone through this as well? What do you guys advice me.
Thank you.