Index tiny ints?

Hi,

I have articles that I want to display by date, (so the latest articles
are always shown first).

So I have an index on the time, (SELECT article FROM tablea ORDER BY
article_time DESC LIMIT 0,5).

Now I want to offer my editors 2 extra options
1- The item is not a frontpage news article
2- The article is marked as 'deleted'

So my select would become

SELECT article FROM tablea WHERE isdeleted=0 and isfrontpage=1 ORDER BY
article_time DESC LIMIT 0,5

isdeleted and isfrontpage will only be 0 or 1
there will be thousands of articles.

Should I create an index on 'tablea'

INDEX idx_article ( article_time, isdelted, isfrontpage)

or should I create a key for each one of them

KEY article_time ( article_time)
KEY article_deleted ( isdeleted)
KEY article_frontpage ( isfrontpage)

What would be the best way to optimize that select?

Thanks

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=24235

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

On 15 Jan, 06:17, FFMG <FFMG.337...@no-mx.httppoint.com> wrote:
> Hi,
>
> I have articles that I want to display by date, (so the latest articles
> are always shown first).
>
> So I have an index on the time, (SELECT article FROM tablea ORDER BY
> article_time DESC LIMIT 0,5).
>
> Now I want to offer my editors 2 extra options
> 1- The item is not a frontpage news article
> 2- The article is marked as 'deleted'
>
> So my select would become
>
> SELECT article FROM tablea WHERE isdeleted=0 and isfrontpage=1 ORDER BY
> article_time DESC LIMIT 0,5
>
> isdeleted and isfrontpage will only be 0 or 1
> there will be thousands of articles.
>
> Should I create an index on 'tablea'
>
> INDEX idx_article ( article_time, isdelted, isfrontpage)
>
> or should I create a key for each one of them
>
> KEY article_time ( article_time)
> KEY article_deleted ( isdeleted)
> KEY article_frontpage ( isfrontpage)
>
> What would be the best way to optimize that select?
>
> Thanks
>
> FFMG
>
> --
>
> 'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
> (http://www.journalhome.com/) | 'webmaster Directory'
> (http://www.webhostshunter.com/)
> 'Recreation Vehicle insurance'
> (http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
> redirection service' (http://urlkick.com/)
> ------------------------------------------------------------------------
> FFMG's Profile:http://www.httppoint.com/member.php?userid=580
> View this thread:http://www.httppoint.com/showthread.php?t=24235
>
> Message Posted via the webmaster forumhttp://www.httppoint.com, (Ad revenue sharing).

IMHO, the only indexes that the optimiser would have a hope of using
in this case would be a composite one of
isdeleted + isfrontpage + article_time
or
isfrontpage + isdeleted + article_time