MySQL Query Length?

[AthlonInside]

select * from something where x=1 or x=2 or x=3 ..........

1. Is there a MySQL query length in terms of number of characters?

2. Is there a maximum number of matching criteria after the where clause, ie. x=1 or x=2 ... x=1024 ... x=59623?

I have a list of keys (1000+) where I want to match them to a table. Should I do the search in 1 query as in 2. Or should I do them one by one = 1000+ queries?

[jatar_k]

if you have a set you could do

select * from something where x in (1,2,3,4.....)

or if they are successive you could do

select * from something where x between 1 and 1000
select * from something where x < 1000
select * from something where x > 3 and x < 1003

MySQL Comparison Operators [mysql.com]

[AthlonInside]

Exactly what I need! :)

Another question, can I use set if they are strings instead of numbers?

select * from table where field in (a, b, c ...)?

[jatar_k]

integers, no quotes

select * from table where field in (1, 2, 3 ...)

strings/chars quoted

select * from table where field in ('a', 'b', 'c' ...)

makes me think though if you are using strings instead of chars it will start getting slow, comparisons may take longer.

[coopster]

>>1. Is there a MySQL query length in terms of number of characters?

Not in terms of characters, but bytes. It is controlled by the max_allowed_packet [mysql.com] system variable. You can use SHOW VARIABLES to see your server settings, for example:

SHOW VARIABLES LIKE 'max_allowed_packet'; 
+--------------------+---------+ 
¦ Variable_name......¦ Value...¦ 
+--------------------+---------+ 
¦ max_allowed_packet ¦ 1047552 ¦ 
+--------------------+---------+ 

max_allowed_packet

The maximum size of one packet. The message buffer is initialized to

net_buffer_length bytes

, but can grow up to

max_allowed_packet

bytes when needed. This value by default is small, to catch big (possibly wrong) packets. You must increase this value if you are using big

BLOB

columns. It should be as big as the biggest

BLOB

you want to use. The protocol limits for

max_allowed_packet

is 16M in MySQL 3.23 and 1G in MySQL 4.0.

If Mr. Tabke were using MySQL to store posts in this forum we would have to ask him to set this configuration option at 1G as I can often be caught writing books here...and don't even get me started on how long-winded the moderator can get
;)

You'll get a Packet too large Error [mysql.com] if the

mysqld

server gets a packet bigger than

max_allowed_packet

bytes.

[AthlonInside]

jatar_k and coopster,

You provide me with great information. Thank You & Merry Christmas! :)