• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1166
  • Last Modified:

Practical Limit on Length of MySQLi Query String

I'm building an export application to run against a table with about 20,000 rows.  Each row needs to be written to the output file, then the row will be marked by changing the "exported" column from default 'N' to 'Y' so that we know which rows have been exported.

The idea of running 20,000 update queries makes me itch, so I was thinking along the lines of using a single query with a clause like WHERE id IN (1,2,3,4,5 ... 20000)

It seems like this query string might be nearly 100,000 characters.  

Is there a practical limit on the length of the query string?  Or any other "gotcha" lurking in this idea?

Is there a "best practice" way of doing something like this?

Response time is not a big issue; this will probably run once a day.

Thanks and regards, ~Ray
0
Ray Paseur
Asked:
Ray Paseur
  • 2
1 Solution
 
mankowitzCommented:
Ray-

Never thought I'd be answering one of your questions, but here goes:

Look at the max_allowed_packet variable to see how big a query you can submit. However, my guess is that you could probably make a query like WHERE id < 20000 in many cases.

SHOW VARIABLES LIKE 'max_allowed_packet';

Open in new window

0
 
Ray PaseurAuthor Commented:
Great!  That's exactly what I was looking for.

stdClass Object
(
    [Variable_name] => max_allowed_packet
    [Value] => 33554432
)
0
 
Ray PaseurAuthor Commented:
Many thanks! ~Ray
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now