[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Practical Limit on Length of MySQLi Query String

Posted on 2013-10-24
3
Medium Priority
?
1,126 Views
Last Modified: 2013-10-24
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
Comment
Question by:Ray Paseur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 total points
ID: 39598081
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
 
LVL 111

Author Comment

by:Ray Paseur
ID: 39598131
Great!  That's exactly what I was looking for.

stdClass Object
(
    [Variable_name] => max_allowed_packet
    [Value] => 33554432
)
0
 
LVL 111

Author Closing Comment

by:Ray Paseur
ID: 39598132
Many thanks! ~Ray
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question