Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query result Fetch Time is way beyond than Execution Time, how to Solve this?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search