Selects with multiple categories

MySQL & MariaDB Other MySQL® Questions
1

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:

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