I need to select data with
optional columns in
where clause based on client input.
my query is like this:
SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id LEFT JOIN c ON a.c_id=c.id where a.status=1 AND (0=@bid OR a.b_id=@bid) AND (0=@cid OR a.c_id=@cid)
@cid are client inputs, the values must be
>0 with default to
0 if client does not provide the input.
client can provide no inputs so it will select all data where
client can provide
@cid or both and the query will select accordingly.
table engine is InnoDB, columns are indexed, foreign key and relationships set.
so far everything is fine.
EXPLAIN SQL shows selection is done based on indexes provided.
now I need to add full-text search on all 3 tables to query is where I have problems. text search is also optional if client provides any keyword to search or not.
fulltext index is defined for a.title, b.txt and c.txt
I changed the query to:
SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id LEFT JOIN c ON a.c_id=c.id where a.status=1 AND ('0'=@keywords OR (MATCH(a.title) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(b.txt) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(c.txt) AGAINST(@keywords IN BOOLEAN MODE))) AND (0=@bid OR a.b_id=@bid) AND (0=@cid OR a.c_id=@cid)
the query seems to be working returning the results I want but the
explain query returns
type=ALL and a full table scan so no the query is not working in an optimized way.
if I change the
OR between matches to
explain query returns
type=fulltext and select is done on FULLTEXT index, but I need the
I was thinking about joining different result sets but couldn't find out how since the input is optional and there might be no input and therefore no full-text search needed to be done.
OK with thanks to jkavalik's comment and Rick's answer it seems I need to add some clarifications:
actually I am using a data service from WSO2 Data Services Server. so I am just passing the input parameters to the data service and I can not generate the select query based on user input. (there's an option to pass part of a query string as input parameter but I am not planning to do that for security reasons)
so I have two options:
write separate queries which accept different sets of input parameters for each case of input parameter combinations. deciding to send the parameters to which query based on user inputs. well the optional inputs being 10+ in a real situation, that will be lots of queries and don't seem to be the right solution. optional fields may change over time, maintenance in future is expensive. doesn't looks like the standard way to handle this.
write a single query in a way it can handle the optional fields.
I prefer option 2 unless there is a third option I am not aware of?
@variables they are either being replaced by input parameter's value or the default value (
0 in this example) before query being passed to and executed in MySQL. and actually they are
@variable but I changed it to
@ in this example for better reading or understandings.