Hiya,
I've been trying to find out about this for a few days, but can't seam
to find much information about it on the web.
At the moment I've got a few tables, one of this looks like:
CREATE TABLE `news` (
`section` TINYINT UNSIGNED ZEROFILL NOT NULL,
`article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
`status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
<more column defitions here...>
PRIMARY KEY (`section`, `article`),
INDEX news_search (`section`, `status`),
)
There is another table with defines the sections of the site, and each
section can have it's own articles. Hence the Primary Key. However, most
of the searches are going to be with the status aswell, as I want to
display articles marked with status='show', i.e.
SELECT * FROM news WHERE section='x' AND status='show';
At the moment, the indexes for section are being duplicated, which I
suppose is a waste of space, and slows the updates (although that's not
a problem, as updates aren't anywhere near as common as searches).
However, would it be better to knock `section` out of news_search and
have just `status`? Does MySQL (currently 4.0.14, although it'll be
running on 3.23.53 eventually) allow the searching of multiple indexes,
or is it better to specify multiple indexes with similar columns to
maximize performance?
Thanks,
--
jonathan wright
// mail at djnauk.co.uk // running on gentoo linux
// life has no meaning unless we can enjoy what we've been given
