Forum Moderators: open
SELECT id, title, article FROM articles WHERE category>0 AND date<NOW() AND priority=1 ORDER BY date DESC
SELECT id, title, article FROM articles WHERE category=1 AND date<NOW() AND priority=4 ORDER BY date DESC
SELECT id, title, article FROM articles WHERE category=19 AND date='2008-02-02 22:22:00' ORDER BY date DESC
SELECT id, title, article FROM articles WHERE category=19 AND subcategory=9 AND date='2008-02-02 22:22:00' ORDER BY date DESC
SELECT id, title, article FROM articles WHERE category=19 AND subcategory=13 AND topic=12 AND date<NOW() ORDER BY date DESC
etc ...
category, subcategory and topic values are numbers from 1-50
date is date
priority values are numbers from 1-5
How would you suggest to add indexes on table 'articles' which looks something like this:
`id` int(9) unsigned zerofill NOT NULL,
`category` tinyint(4) NOT NULL default '0',
`subcategory` tinyint(3) unsigned default '0',
`topic` tinyint(4) unsigned default '0',
`title` text,
`subtitle` varchar(255) default NULL,
`lead` text,
`text` text,
`source` varchar(200) default NULL,
`author` tinyint(4) default NULL,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`priority` tinyint(4) default NULL,
`hits` int(11) default '0',
ENGINE=InnoDB DEFAULT CHARSET=latin1And these are the current indexes with cardinality:
Keyname---------Type------------Cardinality-----Field
-----------------------------------------------------
PRIMARY---------PRIMARY---------80589-----------id
datum-----------INDEX-----------80589-----------date
category--------INDEX-----------16--------------category
subcategory-----INDEX-----------42--------------subcategory
priority--------INDEX-----------16--------------priority
photogallery----INDEX-----------2---------------photogallery
video-----------INDEX-----------2---------------video
topic-----------INDEX-----------60--------------topic
author----------INDEX-----------107-------------author
The table has aprox. 50.000 records, and sometimes works very slow.
Should I instead of one index per field, add one index with multiple columns for most used queries (i.e. category, date and priority)? What type of indexes do you suggest?
