Announcement Module
No announcement yet.

Selects with multiple categories

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selects with multiple categories

    We are using application (which is quite optimized, but not in this particular case) which stores posts in various categories.
    The category field contains comma-separated list of category-ids and so the select query looks like this:
    SELECT ... WHERE category regexp '[[:<:]](124|15|18)[[:>:]]'
    which of course can't use any indexes.

    Is it somehow possible to optimize/rewrite this query without changing the table structure? I know the best would be to have special table with multiple rows of post_id+categ_id, but that would require a lot of work.

  • #2
    Yes, it's better to have another table which stores information about post matching with category.

    But there is a trick, which could increase performance of such queries. What MySQL version and what storage engine are you using? In 5.1 there is a MyISAM mmap feature. Please see details here


    • #3
      I'm using Mysql 5.0.45 (debian package from dotdeb) and this table is MyISAM (it has fulltext index also).
      I'll by adding more RAM (this server has now only 1 GB) and that should help also with the filesystem cache (those tables are about 2x 300 MB + about the same for indexes).
      That mmap feature looks interesting, but I didn't find any debian package for etch (only in experimental).