Bart Bart - 4 months ago 11
MySQL Question

How to search in a two tables with RLIKE where one table can be empty

I have the following query:

SELECT p.partid, pp.partno, p.descr
FROM part p, partcreditor pc, partcreditor_partno pp
WHERE p.partid = pc.partid
AND pc.creditorid = pp.creditorid
AND p.partid = pp.partid
AND (p.partid RLIKE '$val' OR pp.partno_search RLIKE '$val' OR p.descr RLIKE '$val')

For each seperate search term that the user inputs another line like the last line in the query gets added.

This query works very fast but the problem is that table
does not have a record for table
so than nothing gets returned. So I tried rewriting the query with
left joins
but this made the query very slow.

Any solutions?


Theres no getting around it... You need left joins.

However, try moving the criteria up into the join condition, like so:

SELECT p.partid, pp.partno, p.descr
FROM part p
JOIN partcreditor pc ON p.partid = pc.partid
LEFT JOIN partcreditor_partno pp ON p.partid = pp.partid
  AND pc.creditorid = pp.creditorid
  AND pp.partno_search RLIKE '$val'
WHERE p.partid RLIKE '$val'
OR p.descr RLIKE '$val'

It's a little odd that you're doing an RLIKE on an id column.

Since you're doing text searching, you will find that using a FULLTEXT index will improve performance astoundingly.