| Multiple categories and index problems [message #3269] |
Wed, 09 July 2008 19:34  |
 armakuni
Messages: 3
Registered: July 2008 |
Junior Member |
|
|
Hi!
I have a following table:
CREATE TABLE items (
id INTEGER UNSIGNED NOT NULL,
dt DATETIME NOT NULL,
title VARCHAR(1024) NOT NULL,
cat1 TINYINT UNSIGNED NOT NULL,
cat2 TINYINT UNSIGNED,
cat3 TINYINT UNSIGNED,
cat4 TINYINT UNSIGNED,
...
PRIMARY KEY (id),
INDEX (dt)
) ENGINE = MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
The idea is to search items from the table using the categories. There are only four possible categories from which at least category 1 exists. The results are ordered by time. The problem is that the queries can be very different like:
- SELECT ... WHERE cat1 = 10 OR cat2 = 10 OR cat3 = 10 OR cat4 = 10 ...
- SELECT ... WHERE (cat1 = 10 OR cat2 = 10 OR cat3 = 10 OR cat4 = 10) AND (cat1 <> 20 AND cat2 <> 20 ...
- SELECT ... WHERE (cat1 NOT IN (13, 14, 15)) OR (cat2 NOT IN (13, 14, 15)) ...
The combinations are very different. The problem is that I have not found any good solution for indexes so that they would limit the searched rows. If I add an own index for every category it does not help. Also using multiple-column index does not seem to help. Mostly MySQL only uses time index and if category indexes get used it results filesort.
Any help appreciated. Also table structure can be changed if there is a better solution for this kind of problem.
|
|
|
| Re: Multiple categories and index problems [message #3305 is a reply to message #3269 ] |
Wed, 16 July 2008 07:16  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
Have you considered implementing a separate categories table which maps the categories?
E.g.:
table: categories_map
doc_id INT,
cat_id TINYINT
PK(cat_id, doc_id)
Martin Gallagher | Speeple: The latest news
|
|
|
|
Members
Search
Help
Register
Login
Home
