MySQL select collections matching multiple subgenres

Assuming these two (simplified) tables:

CREATE TABLE books (
    book_id VARCHAR(25) NOT NULL,
    subgenre_1 VARCHAR(6) NOT NULL,
    subgenre_2 VARCHAR(6),
    subgenre_3 VARCHAR(6),
    mood_1 VARCHAR(4) NOT NULL,
    mood_2 VARCHAR(4),
    mood_3 VARCHAR(4),  
    PRIMARY KEY (book_id)
);

CREATE TABLE collections (
    collection_id VARCHAR(25) NOT NULL,
    subgenre_1 VARCHAR(6) NOT NULL,
    subgenre_2 VARCHAR(6),
    subgenre_3 VARCHAR(6),
    mood_1 VARCHAR(4) NOT NULL,
    mood_2 VARCHAR(4),
    mood_3 VARCHAR(4),
    PRIMARY KEY (collection_id)
);

I'm trying to display the collections that match a specific book.

$bk_sg1 = "FICT";
$bk_sg2 = "HIST";
$bk_sg3 = "JUVE";
$bk_md1 = "ROMA";
$bk_md2 = "TENS";
$bk_md3 = "EMOT";

SELECT collection_id FROM collections
WHERE (subgenre_1 = $bk_sg1 OR subgenre_2 = $bk_sg1 OR subgenre_3 = $bk_sg1) OR
    (subgenre_1 = $bk_sg2 OR subgenre_2 = $bk_sg2 OR subgenre_3 = $bk_sg2) OR
    (subgenre_1 = $bk_sg3 OR subgenre_2 = $bk_sg3 OR subgenre_3 = $bk_sg3) OR
    (mood_1 = $bk_md1 OR mood_2 = $bk_md1 OR mood_3 = $bk_md1) OR
    (mood_1 = $bk_md2 OR mood_2 = $bk_md2 OR mood_3 = $bk_md2) OR
    (mood_1 = $bk_md3 OR mood_2 = $bk_md3 OR mood_3 = $bk_md3)

Is there a more efficient way to do the SELECT statement? The collections table will be very large eventually and I don't want to worry about efficiency in the future if I can avoid it.

Solution

To improve readability and maintainability, you can use IN() instead of multiple OR conditions:

SELECT collection_id 
FROM collections
WHERE subgenre_1 IN ('$bk_sg1', '$bk_sg2', '$bk_sg3')
   OR subgenre_2 IN ('$bk_sg1', '$bk_sg2', '$bk_sg3')
   OR subgenre_3 IN ('$bk_sg1', '$bk_sg2', '$bk_sg3')
   OR mood_1 IN ('$bk_md1', '$bk_md2', '$bk_md3')
   OR mood_2 IN ('$bk_md1', '$bk_md2', '$bk_md3')
   OR mood_3 IN ('$bk_md1', '$bk_md2', '$bk_md3');

Add indexes on subgenre_1, subgenre_2, subgenre_3, mood_1, mood_2, mood_3 for faster searching

CREATE INDEX idx_subgenres ON collections (subgenre_1, subgenre_2, subgenre_3);

CREATE INDEX idx_moods ON collections (mood_1, mood_2, mood_3);