I'm implementing a typical list Rest endpoint /items with some optional filtering URI query parameters like ?attr=val&attr2=val etc..

The Rest server is backed with Go/MySQL

About query performances, i wonder if it is better to create a prepared statement which make uses of LIKE statements :

SELECT cols from items WHERE attr LIKE ? and attr2 LIKE ?;

and simply set values to '%' to attributes not filled by the user

or generate the query on the fly based on given attrs ?

Exemple with no attrs:

SELECT cols from items;

Exemple with one attr:

SELECT cols from items where attr LIKE 'val';

More generally i wonder if using LIKE '%' has a performance cost (considering indexes are configured properly on theses cols). And if theses performances costs are worth in a prepared statement compared to the cost of generate the query on the fly (parsing etc.).

Note: The number of distinct filtering attrs beeing pretty significant it is not conceivable to generate specific prepared statement for every possible attrs combination.

CodePudding user response：

There are thee parts involved when you are doing a query.

1. Parsing the query.
2. Optimizing the query based on the query structure and the used parameter values.
3. Doing the query using the optimized query.

The query optimizer technically could optimize LIKE '%' away to something not using LIKE, but it seems as if MySQL doesn't do that (but I'm not 100% sure about that).

For booleans, the query optimizer however does such optimizations.

If you do:

SELECT * FROM test WHERE (attr='val' OR TRUE) AND (attr2='val' OR FALSE);

The resulting query will be:

SELECT * FROM test WHERE attr2='val';

Because (attr='val' OR TRUE) will always be TRUE, and OR FALSE doesn't do anything.

So you could always have something like:

SELECT * FROM test WHERE (attr=@attr OR !@useAttr) AND (attr2=@attr2 OR !@useAttr2);

And enable/disable the usage of the corresponding filter using a boolean.

Or something like this if the value is null if it is not set:

SELECT * FROM test WHERE (attr=? OR ISNULL(?)) AND (attr2=? OR ISNULL(?));

And call the query like that stmnt.execute(attr, attr, attr2, attr2).