MySQL - how to optimize large set of conditions

Solution 1:

The correct data structure is one row per pid and code. The simplest way is:

create table PCodes (
    pid int not null,
    code varchar(255),
    constraint fk_PCodes_pid references p(pid)
);

Then you have the values in a single column and it is much simpler to check for matching codes.

In practice, you should have three tables:

create table Codes (
    CodeId int not null auto_increment primary key,
    Code varchar(255)
);

create table PCodes (
    pid int not null,
    codeid int not null, 
    constraint fk_PCodes_pid references p(pid),
    constraint fk_PCodes_codeid references codes(codeid);
);

If the ordering of the codes is important for each "p", then include a priority or ordering column in the PCodes table.