Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Multiple categories and index problems

armakuniarmakuni EntrantCurrent User Role Beginner
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;</pre>


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)) ...</pre>


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.

Comments

  • SpeepleSpeeple Contributor Inactive User Role Beginner
    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)
  • armakuniarmakuni Entrant Current User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Speeple wrote on Wed, 16 July 2008 14:16</td></tr><tr><td class="quote">
    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)
    </td></tr></table>
    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. )
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.