Wildcard in MYSQL

[silversurfer5150]

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

[Mikl]

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:

$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

[Mikl]

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

[cpradio]

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.

[r937]

// 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

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

WHERE 1=1 -- all manufacturers, all prices

WHERE 1=1 AND manufacturer="samsung"  --  all prices

WHERE 1=1 AND price = 9.37 --  all manufacturers

WHERE 1=1 AND manufacturer="samsung" AND price = 9.37

[Mikl]

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

[r937]

... 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

...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

[cpradio]

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.

[r937]

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?

[cpradio]

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

[cpradio]

From MySQL version 5.1.63

Query:

EXPLAIN SELECT *   `COLUMNS`

Explain results:

1	SIMPLE	COLUMNS	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases

Query:

EXPLAIN SELECT *   `COLUMNS` WHERE COLUMN_NAME LIKE '%'

Explain results:

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

[r937]

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?

[cpradio]

Trying this again:
From MySQL version 5.1.63 using the information_schema table

Query:

EXPLAIN SELECT *  FROM `COLUMNS`

Explain results:

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:

EXPLAIN SELECT *  FROM `COLUMNS` WHERE COLUMN_NAME LIKE '%'

Explain results:

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

[r937]

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 --

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.)

[cpradio]

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.'