Hi all, I'm writing a php function to search things in a mysql database. The query will basically look like SELECT column, column2, .... WHERE email LIKE ?, first_name LIKE ?, last_name LIKE ?... When the user leaves a field blank (to not filter on that field), is it better to just leave the field with the like in the query, or is it faster to do a check in my php code and leave the field out of the query? So basically I'm asking what's the fastest. Letting the db-server LIKE columns with empty strings or let php do an if-check on every wearch field and only add the column to the where clause when the search value != "";
Check out this answer to a similar question on StackOverflow. Personally, I think I would try to use the database queries for as much as possible.
Hi Stannieman. There's a little server resources consumption when adding any other column at the query conditional, because the server will also need to compare the column with the fixed value for every row. Removing the unused conditional from the final query is the way. I also will strongly recommend the use of parameters around the query, it will kill the sql injection vulnerability and also make your code clean to maintain. http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html
Ok thanks Josh! The thing is you need to view the total picture. Reducing load on the DB-server by leaving columns out will increase the load on the webserver because PHP needs to do additional checks. And it might very well be that the PHP overhead becomes way larger than the SQL overhead. For the parameters, I'm using that already, security first! EDIT: Something completely else: I have an order by clause where I add columns to according to the columns in the select clause. Is it ok to do something like ORDER BY null, column1, column2. The null then just acts as a dummy, because otherwise you'd get ORDER BY , column1... with the comma being wrong there. I don't know which column will come first in the order by clause. It works with null as dummy, but I don't know if this should be done.