MySQL Forums
Forum List  »  Performance

Range optimization for articles programmed by date
Posted by: René Olivo
Date: November 16, 2011 12:13AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Range optimization for articles programmed by date
1794
November 16, 2011 12:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.