SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist silversurfer5150's Avatar
    Join Date
    Aug 2010
    Posts
    534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Wildcard in MYSQL

    Hi guys,

    I am trying to design a database call to operate from a filter control so that I can select things out of the DB depending on which options are selected on the UI. However I need to first check that the syntax I have in mind will work with MYSQL as I don't use many statements of this type.

    For example I know you can use the SELECT * from DB method but what if I were to pass it a php variable "*" would that then allow me to go:

    SELECT * from DB WHERE manufacturer="samsung" and price = $var // var is a string "*"

    which would then filter a specific manufacturer but disregard whether the user had selected a particular price range so my meaning is get any price

    or vice versa could I go

    SELECT * from DB WHERE manufacturer=$var and price >= "50" /. to get all manufacturers but a particular price?

    I know the punctuation is a little off but its only an illustration, I would be very grateful if someone could help me

    kind regards

    Silversurfer
    "Persistence is the path to perfection"

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,571
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    If I've understood this right, you are asking if you can place an asterisk in a variable to match any string in a WHERE clause.

    The answer to that is No. It doesn't work like that.

    One solution would be to build the command programmatically. In pseudo-code, it would be something like this:

    Code:
    $command = "SELECT * from DB "
    if (match a specific manufacturer)
      $command = $command . "Manufacturer = ' . $var_containing_manufacturer's_name;
    // similarly for price
    Execute the command;
    Hope this makes sense. There might be better ways to do it, in which case someone more knowledgeable than me will chip in.

    Mike



  3. #3
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,571
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    It's just dawned on me that there's a much better way of doing this: the LIKE clause.

    Some examples:

    // Match all manufacturers
    SELECT * from DB WHERE Manufacturer LIKE '%'

    // Match a particular manufacturer
    SELECT * from DB WHERE Manufacturer LIKE 'Samsung'

    // Match manufacturers beginning with Sam
    SELECT * from DB WHERE Manufacturer LIKE 'Sam%'


    // Match manufacturers with XX in the name
    SELECT * from DB WHERE Manufacturer LIKE '%XX%'

    and so on.

    Mike

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by silversurfer5150 View Post
    Hi guys,

    I am trying to design a database call to operate from a filter control so that I can select things out of the DB depending on which options are selected on the UI. However I need to first check that the syntax I have in mind will work with MYSQL as I don't use many statements of this type.

    For example I know you can use the SELECT * from DB method but what if I were to pass it a php variable "*" would that then allow me to go:

    SELECT * from DB WHERE manufacturer="samsung" and price = $var // var is a string "*"

    which would then filter a specific manufacturer but disregard whether the user had selected a particular price range so my meaning is get any price

    or vice versa could I go

    SELECT * from DB WHERE manufacturer=$var and price >= "50" /. to get all manufacturers but a particular price?

    I know the punctuation is a little off but its only an illustration, I would be very grateful if someone could help me

    kind regards

    Silversurfer
    Sure, but you don't need to provide a *...

    If you want a specific manufacturer without a specific price range, your query is SELECT * from DB WHERE manufacturer="samsung"

    If you want a specific price range and any manufacturer, you want SELECT * from DB WHERE price >= "50"

    To do this in a programmatic way, just check your manfacturer and price variables for a * (or whatever character you use in your UI form) and strip out the clause in your WHERE statement accordingly.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    // Match all manufacturers
    SELECT * from DB WHERE Manufacturer LIKE '%'
    this will work efficiently provided that mysql is smart enough to optimize away the WHERE condition

    in my opinion, it's better not to generate the WHERE condition in the first place, if you want all manufacturers

    but it can get tricky if there is more than one condition, because then there's the question of which one comes right after the WHERE keyword, and which one comes after the AND keyword

    the neat workaround for this is to begin each WHERE clause with
    Code:
    WHERE 1=1
    which i know does get optimized away

    then in deciding whether other conditions are to be generated, all you have to do is preface each of them with AND

    Code:
    WHERE 1=1 -- all manufacturers, all prices
    Code:
    WHERE 1=1 AND manufacturer="samsung"  --  all prices
    Code:
    WHERE 1=1 AND price = 9.37 --  all manufacturers
    Code:
    WHERE 1=1 AND manufacturer="samsung" AND price = 9.37
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,571
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    this will work efficiently provided that mysql is smart enough to optimize away the WHERE condition
    I'm not sure about MySQL, but in other databases I've worked with, it will optimise if the fixed part is at the start of the string (LIKE 'Sam%'), but not if the wildcard comes first (LIKE '%Sam').

    WHERE 1=1
    Yes, that's a very convenient way of doing it. In fact, can you even just say:

    WHERE 1

    on the basis that 1 is always true? Again, I know other languages that let you do that; not sure about MySQL.

    Mike

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    ... but not if the wildcard comes first (LIKE '%Sam').
    so you're saying it might not optimize LIKE '%' because the wildcard comes first?

    best just not generate it at all, then


    Quote Originally Posted by Mikl View Post
    ...on the basis that 1 is always true?
    no, actually, 1 is always an integer

    mysql "silently" converts it to a boolean (TRUE) and i'm not sure if this works in other databases, but you can be certain that 1=1 always works everywhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so you're saying it might not optimize LIKE '%' because the wildcard comes first?

    best just not generate it at all, then
    The reason why LIKE'%' with the % at the beginning of the string and slower in performance than it being after a character (preferably 3 characters for best performance) is because it forces a table scan. When you have at least 3 characters you can make use of indexes on the table to prevent a table scan.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    The reason why LIKE'%' with the % at the beginning of the string and slower in performance than it being after a character (preferably 3 characters for best performance) is because it forces a table scan.
    i think we all understand that...

    the question was, does LIKE '%' -- specifically, a string consisting of just the wildcard, which is supposed to return everything -- also force a table scan?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i think we all understand that...

    the question was, does LIKE '%' -- specifically, a string consisting of just the wildcard, which is supposed to return everything -- also force a table scan?
    Well that is a good question. Depends on how smart the SQL translator is. I'd have to watch it through an explain process, but I bet most SQL translators would remove the LIKE clause entirely

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    From MySQL version 5.1.63

    Query:
    Code:
    EXPLAIN SELECT *   `COLUMNS`
    Explain results:
    Code:
    1	SIMPLE	COLUMNS	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
    Query:
    Code:
    EXPLAIN SELECT *   `COLUMNS` WHERE COLUMN_NAME LIKE '%'
    Explain results:
    Code:
    1	SIMPLE	COLUMNS	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned all databases
    As you can see MySQL does use the WHERE clause. Not sure if others would too, but it seems it is best to remove such a clause in MySQL

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    Code:
    EXPLAIN SELECT *   `COLUMNS`
    that's not even a valid query

    were you a little too ruthless in your obfuscation?

    also, i've never encountered "open_frm_only" before, but it appears to be used only with the information_schema... can you shed some more light on this?

    what table(s) did you actually run your queries on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Trying this again:
    From MySQL version 5.1.63 using the information_schema table

    Query:
    Code:
    EXPLAIN SELECT *  FROM `COLUMNS`
    Explain results:
    Code:
    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE		COLUMNS	ALL	NULL		NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
    Query:
    Code:
    EXPLAIN SELECT *  FROM `COLUMNS` WHERE COLUMN_NAME LIKE '%'
    Explain results:
    Code:
    id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    1	SIMPLE		COLUMNS	ALL	NULL		NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned all databases
    As you can see MySQL does use the WHERE clause. Not sure if others would too, but it seems it is best to remove such a clause in MySQL

    As for the Open_frm_only, this describes what MySQL had to do to get to the data, it had to open the .frm file only.
    http://dev.mysql.com/doc/refman/5.5/...#explain_extra

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    we may be barking up the wrong tree with these over-simplified queries

    using the dreaded, evil "select star" and without any WHERE clause, mysql will always choose a table scan... think about it

    a better test would be a SELECT clause which can be satisfied by an index, something like this --
    Code:
    SELECT employee_id FROM employees -- assumong employee_id is indexed
    then adding WHERE somecolumn LIKE '%' on some non-indexed column

    this should reveal more clearly whether mysql ignores the obviously useless condition ("optimizes it away")

    on the other hand, i can think of one very good reason why it would ~not~ optimize it away -- it will be forced to look at the column values if the column is nullable!! (because NULL is not LIKE anything, ever)

    my conclusion is unchanged from what i stated earlier -- it's better to leave off that condition if you simply want to return all rows (i.e. when you don't wish specific manufacturers, or specific prices, etc.)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,164
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    I wasn't particularly looking for a table scan, but rather if the WHERE clause was being utilized when querying on LIKE '%'. My simplified example shows that MySQL still uses the WHERE clause when the ONLY clause in the WHERE statement is LIKE '%'. So in regards to would MySQL even run the WHERE clause if the only clause contained LIKE '%', the answer is yes, it would. That may not be a true statement for other databases, but it does answer it for MySQL.

    I also queried an indexed column and a non-indexed column (not using SELECT *) and I got the exact same results. I could load a fake table up with all sorts of fake data, but I think the initial question on if the WHERE clause would even be used if it only contained COLUMN LIKE '%' will remain as 'yes, it will.'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •