1

I am using MySQL 8.0.27-18, with InnoDB as engine. I have a huge database, with few tables containing 100M+ rows in them. For example: TABLE_A with 110M and TABLE_B with 300M+ rows. Sometimes I had to join these tables in fact, and the even worst part is, need to perform SORTing on the results.

Instead of going with complex queries, I wanted to start with a simple query like the SELECT statement below,

SELECT MERCH_NUM,TRANS_DATE, TRANS_AMOUNT, ETC... FROM TABLE_A WHERE MERCH_NUM = 'XXXXYYYYZZZZ' ;

This table TABLE_A have 110M rows, and the given MERCH_NUM have ~9M rows. Also, we have an Index on the MERCH_NUM column too. Now, I can able to execute this query at the cost of Minutes. So, when I looked at statistics, I see below.

Duration / Fetch

0.78 sec / 270.782 sec

As I understood, MySQL took less than 1 sec to complete the query execution but took forever to fetch or retrieve or transfer the results data to client, If I am not wrong.

This fetch part became a huge bottleneck to my application now.

What is causing this huge fetch times, particularly when dealing with huge data? And, How to resolve this?

P.S: Our server has 24GB of RAM allocated.

Improve this question
21
  • It could be a limitation of network transfer speed. What can you tell us about the network link between the client and the server? For example, is it within the same datacenter? Or is it going over a slow link between regions? What is the size of the result measured in bytes instead of rows? Commented Apr 22, 2024 at 15:25
  • look for explain SELECT to see what is happening, maybe it takes the wrong indesx, also there are other types of indexes which you should test Commented Apr 22, 2024 at 15:26
  • @nbk That will affect execution time, not fetch time. Commented Apr 22, 2024 at 15:26
  • 1
    How much data is being fetched? Commented Apr 22, 2024 at 15:27
  • 1
    IN ( SELECT ... ) is quite inefficient. Can you turn tha into a JOIN? It sounds like the system is giving the user too much flexibility in constructing the query -- heading to inefficient queries on the large table. Commented Apr 26, 2024 at 14:44

2 Answers 2

Reset to default
1

Getting the first row of your result set is a subsecond operation, so that indicates an index was used to satisfy your query.

Getting the rest of the rows looks like it took 4.5 minutes. That indicates your result set had many rows and a large volume of data. It looks from comments like you're fetching nine megarows?

A lot of factors can slow the fetching of large volumes of data. It has to move over the network, for one thing. The program receiving it has to do something with it, for example writing it to a file, or maybe allocating RAM for it. The fetch step can't complete until your program reads the last row. But you didn't mention what your program does with the data, so it's hard to know exactly what the cause of your slowdown is.

At any rate, there's no weird SQL voodoo that makes bulk data transfers instantaneous. You probably should consider a different way of getting your results that doesn't involve transferring megarows. Often, the queries you call "complex" can be satisfied quite quickly with appropriate indexes.

Or, simply be prepared for such operations to take time.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Jones, for responding. Learned a new thing from the response to this post about the Duration time. I understood more when I tried the queries with LIMIT, OFFSET. The deeper I go into the result set (using offset/ LIMIT 1000 OFFSET 100000) the longer query response takes. Then I got to know there not much impact from the Fetch time in my case.
Do you know where I am missing ? My application will apply filters like Date Range and Merchant Number, and the response should be SORTED with various columns like DATE or HOLDER NAME or any other numeric/alphanumeric columns.
This problem is called "query pagination." It is a notoriously difficult problem to solve in a way that gives the flexibility you want. Here is a way I have tried to mitigate it. stackoverflow.com/questions/67833698/…
0

Need these indexes:

B:  INDEX(HQ_MRCH_ACCT, HQ_MRCH_ACCT)  -- in this order
A:  INDEX(MERCHANT_NBR)

If those don't help, please edit the Question to include SHOW CREATE TABLE and EXPLAIN SELECT.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.