Overhead of LIKE in SQL query

Discussion in 'Mixed Languages' started by Stannieman, Feb 16, 2015.

  1. Stannieman

    Stannieman MDL Guru

    Sep 4, 2009
    2,232
    1,800
    90
    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 != "";
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. Calistoga

    Calistoga MDL Senior Member

    Jul 25, 2009
    420
    198
    10
    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.
     
  3. Stannieman

    Stannieman MDL Guru

    Sep 4, 2009
    2,232
    1,800
    90
    Ok thanks, I know what to do now ;)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,106
    120
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Stannieman

    Stannieman MDL Guru

    Sep 4, 2009
    2,232
    1,800
    90
    #5 Stannieman, Apr 7, 2015
    Last edited: Apr 10, 2015
    (OP)
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...