11

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.

Bakhtiyor
  • 7,198
  • 15
  • 55
  • 77
  • 2
    I believe your main concern should be the heavy use of `LIKE` (it's expensive), not the _length_ of the query string *per se* :) – jensgram Jun 11 '10 at 20:49
  • Any suggestions to replace LIKE into something else? – Bakhtiyor Jun 11 '10 at 20:53
  • 1
    a FULLTEXT index might be appropriate. – Frank Farmer Jun 11 '10 at 21:00
  • Thank you very much for all participants of this question. I highly appreciate your help. I really love stackoverflow for its potential users who always ready to help you. After all these critiques on my sql query I decided to change my DB design in order to skip using LIKE statement. So now I will not use anymore this bloody LIKE. New design has been proposed also here by this community and you can see it here - http://stackoverflow.com/questions/3039077/need-advice-to-change-my-database-design. Thanks. – Bakhtiyor Jun 14 '10 at 17:56
  • possible duplicate of [What is maximum query size for mysql?](http://stackoverflow.com/questions/16335011/what-is-maximum-query-size-for-mysql) – pmaruszczyk Aug 26 '15 at 08:11

2 Answers2

19
  1. (if possible) Use WHERE metadata IN ('value1', 'value2')
  2. You may need to increase max_allowed_packet. It defaults to 16MB (client-side, and as low as 1MB server-side in older versions), 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

Frank Farmer
  • 38,246
  • 12
  • 71
  • 89
  • but where metadata in ('value1','value2') retrieves only when metadata exactly equal to value1 or value2. In my case I need to retrieve similar metadatas, i.e. I need to consider that 'sql' and 'mysql' are equal strings. – Bakhtiyor Jun 11 '10 at 20:52
  • 1
    `WHERE metadata IN ('value1', 'value2')` will not do the same thing as he mentions. It will not look to see if 'value' is inside metadata, but rather will go through each value in the list ('value1', 'value2') to see if any item exactly matches metadata. However, the OP will definitely want to check out `max_allowed_packet` as mentioned if is concerned with dozens of where clauses. – Joseph Jun 11 '10 at 21:02
  • The doc says: *"Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server. If you are using the mysql client program, its default max_allowed_packet variable is 16MB"* So it seems that the default is 16MB for the mysql client program only. – Pang Sep 01 '16 at 07:51
  • 1
    On the server, the default is [[1M for 5.5](http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet)] | [[1M for <= 5.6.5, 4M for >= 5.6.6](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet)] | [[4M for 5.7](http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet)]. – Pang Sep 01 '16 at 07:51
5

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

ircmaxell
  • 163,128
  • 34
  • 264
  • 314