Multiple categories and index problems

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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)

3
[B]Speeple wrote on Wed, 16 July 2008 14:16[/B]
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)

Thanks for reply Speeple. An interesting idea and perhaps I should give it a try. I only thought how to make indices work but a different approach might be a good idea. )