Search functionality using joins

Question

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.

Answer 1

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.