MySQL Performance: MySQL => Multiple categories and index problems

Multiple categories and index problems [message #3269]

Wed, 09 July 2008 19:34

armakuni
Messages: 4
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.

Report message to a moderator