Mysql – Search functionality using joins

MySQL

I have three tables as mentioned below. I need to implement basic search functionality where the user will search for a keyword which could either match 'title' (from Proposal table) or 'msNumber' or 'description' (from Project table) and should match 'stageNumber' and 'newState' from ProjectLifecycle table.

create table Proposal (
    proposalId LONG not null primary key,
    title VARCHAR(75) null,
);

create table Project (
    proposalId LONG not null primary key,
    msNumber VARCHAR(75) null,
    description VARCHAR(75) null
);

create table ProjectLifecycle (
    lifecycleId LONG not null primary key,
    proposalId LONG,
    stageNumber DOUBLE,
    newState LONG,
);

Below is the query which am using right now

SELECT
    p.proposalId, p.title, pj.msNumber
FROM
  Proposal p
JOIN
  ProjectLifecycle pl ON pl.proposalId = p.proposalId
JOIN
  Project pj ON pj.proposalId = pl.proposalId
WHERE
  (p.title like '%%' or pj.msNumber like '%%')
AND
    pl.newState=0
AND
  (pl.stageNumber= 60.60 OR pl.stageNumber < 60.60 OR pl.stageNumber = 95.99);

The stageNumber and the newState conditions are working fine as expected but the keyword search isn't.

Please help me in finding where I am going wrong. I am using MySQL which does not support FULL OUTER JOIN.

Best Answer

How would you write the FULL OUTER JOIN if it were available. (I don't see the need for it in your case.)

p.title like '%%' is a costly no-op. Remove it from the WHERE clause. (I assume you are building the WHERE on the fly? If so, make your code a little smarter.)

pl.stageNumber = 60.60 OR
pl.stageNumber < 60.60

-->

pl.stageNumber <= 60.60

ORs are costly because they often make it so the index cannot be used.

Are those stage numbers always dd.dd ? If so, use DECIMAL(4,2). DOUBLE has potential rounding errors, an may fail to match when you expect.

Related Question