Rewriting query in order to remove FIND_IN_SET?

Question

My mysql query looks like this:

    SELECT pages.*,
           showcase.*,
           project.*    
      FROM pages
INNER JOIN showcase ON showcase.pid = pages.uid AND showcase.deleted != 1
INNER JOIN project ON FIND_IN_SET(project.uid, showcase.projects)   
     WHERE pages.deleted != 1
       AND pages.pid = 14
       AND pages.dokType = 150

The problem is the second INNER JOIN - it uses FIND_IN_SET because a showcase (= collection of projects) stores its projects as a comma separated list in the field showcase.projects. FIND_IN_SET cant use indexes as far as I know, so the second join requires a full table scan of the project table. Any possibility to use an index without changing the database scheme?

Answer 1

Is showcase.projects a VARCHAR? Then you could use REGEXP like this:

...
INNER JOIN project ON
showcase.projects REGEXP CONCAT('[[:<:]]', project.uid, '[[:>:]]')
WHERE ...

This expression looks for whole words only (the bracket constructions mark left and right word boundaries, respectively. However, I'm not sure this will be able to make use of an index, maybe someone can come up with something smarter.

Answer 2

You searching for a 'string' inside another string. You'll have to do a scan regardless. The other optimized way to do this using indexes is to use a join table. Create a new table showcase_projects that has columns of project_id and showcase_id. This will then have a record for every association between the two.

Now this answer is based on my primitive understanding of your datastructure from this query.