Is there a limitation to the length of the query in mysql?

Question

I am asking this question because I need to know this limitation as I am generating SELECT query in my PHP script and the part of WHERE in this query is generated inside the loop.
Precisely it looks like this

 $query="SELECT field_names FROM table_name WHERE ";
 $condition="metadata like \"%$uol_metadata_arr[0]%\" ";
 for($i=1; $i<count($uol_metadata_arr); $i++){
    $condition.=" OR metadata like \"%$uol_metadata_arr[$i]%\" ";
 }
 $query.=$condition;
 $result=mysql_query($query);

So, that's why I need to know how long my $query string can be, because the array *$uol_metadata_arr* could contain many items.

Answer 1

1. (if possible) Use WHERE metadata IN ('value1', 'value2')
2. You may need to increase max_allowed_packet. It defaults to 16MB, and it's not that hard to construct a query that runs up against that limit (e.g., importing data from elsewhere with a giant INSERT query)

LIKE '%string%' is a performance killer. Such a query can't use an index on that column. LIKE 'string%' on the other hand, is indexable

Answer 2

See the max_allowed_packet global variable. You'll need access to the my.cnf file to adjust it (and need to adjust it on your client as well). The typical defaults are either 1mb or 16mb...