Working with MySQL query limit of 65535 rows - how to fetch all data?

Other Questions MySQL
1

I’m facing a limitation with my MySQL query that only allows me to retrieve up to 65535 rows. However, my actual dataset has around 120,000 entries. I’m looking for a way to access the entire dataset without being restricted by this limit.

Here’s the code I’m using that leads to this issue:

query = "SELECT DISTINCT user_id FROM orders"
print(query)
try:
    db_cursor.execute(query)
    unique_ids = db_cursor.fetchall()
    print("Query executed successfully")
    print("Retrieved %d unique user IDs" % len(unique_ids))

What solutions exist for me to extract all the records? Should I implement pagination or look into adjusting some settings?

2

The 65535 limit you’re encountering likely stems from your MySQL client settings rather than the database itself. It’s advisable to review the max_allowed_packet configuration and if you’re using a specific connector, consider the cursor’s arraysize parameter.

As a quick remedy, you might want to implement cursor-based pagination using OFFSET and LIMIT. For example, you can execute a query like SELECT DISTINCT user_id FROM orders LIMIT 10000 OFFSET 0, adjusting the offset with each subsequent call. Do note that large offsets may lead to performance issues as MySQL still has to process all earlier rows.

For more efficient alternatives, explore the use of server-side cursors if supported by your connector, or implement keyset pagination. This method allows you to order your results and utilize WHERE clauses, commencing from the last retrieved value for the next segment.

3

I’ve hit this same issue with large MySQL datasets. It’s usually the Python connector config, not MySQL itself. Set buffered=False when creating your cursor - stops the client from loading everything into memory at once. What really helped me was chunking the data retrieval. Skip fetchall() and use fetchmany(size) in a loop instead. Way more memory-efficient and dodges those client-side limits. If you absolutely need everything at once, try bumping up your connector’s buffer size or switch to a streaming cursor. MySQLdb and mysql-connector-python handle big result sets differently, so your fix depends on which driver you’re running.

4

Use fetchone() in a while loop instead of fetchall() - way less memory intensive. Something like while True: row = cursor.fetchone(); if not row: break works better for huge datasets. Also check if your connector has a stream=True parameter - that usually bypasses client buffering limits completely.