2

I have a single table containing around 280 million rows in MariaDB 10.4.10.

I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest factor is the query speed. This query takes around 37 minutes to run (old laptop, SSD, virtual machine with 8GByte RAM). This is normal, I think.

However, the first result is only returned after 17 minutes, in which the client program is just waiting.

Is there a way to make MySQL return the first results faster, even if the total query time is unchanged, or even slightly longer?

Table structure:

CREATE TABLE `eventlog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `moment` datetime NOT NULL,
  `source_id` int(11) NOT NULL,
  `code` binary(5) NOT NULL,
  `category` binary(2) DEFAULT NULL,
  `type` enum('single','double','triple') CHARACTER SET binary NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_moment` (`moment`)
) ENGINE=InnoDB AUTO_INCREMENT=283252852 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It is around 12GByte on disk (data length, excluding indexes). The index is 6.5GByte.

Query:

SELECT
  id,
  moment,
  source_id,
  code
FROM
  eventlog
ORDER BY
  moment ASC

EXPLAIN shows it is not using the index on moment to run the query.

MariaDB [test]> explain select id, moment, source_id, code from eventlog order by moment asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: eventlog
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 282499875
        Extra: Using filesort
1 row in set (0.017 sec)

Profiling the query shows it spends practically all time on "Creating sort index", even though an index on the ORDER BY field already exists. (All other stages in the profile are each less than 0.02s):

*************************** 14. row ***************************
             Status: Creating sort index
           Duration: 999.999999
           CPU_user: 999.999999
         CPU_system: 110.469717
  Context_voluntary: 307668
Context_involuntary: 18439
       Block_ops_in: 74089616
      Block_ops_out: 50343784
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 111
  Page_faults_minor: 6653
              Swaps: 0
    Source_function: <unknown>
        Source_file: sql_select.cc
        Source_line: 21167

I have tried:

  • Forcing the index: I thought this would make sense, since the query is basically returning the rows in index order. EXPLAIN then shows the index will be used, but the first result is even later (175 minutes?!).
  • Increasing sort_buffer_size from 2M to 20M, without effect.
  • Running with LIMIT and OFFSET: the first batch of 500 000 rows is returned in 9 seconds, which is nice, but the total query time (getting all 280 million rows in batches of 500 000) increases drastically as the OFFSET increases, making the total time the external program is idle even longer.

How can I make make MySQL return the first results faster without making the total runtime several times longer?

8
  • 1
    Try a covering index may be?
    – mustaccio
    Dec 3, 2019 at 14:05
  • Can you please post the CREATE INDEX statement(s)? Dec 3, 2019 at 14:52
  • @mustaccio : I'll try that and post the results. Dec 3, 2019 at 15:19
  • @MichaelKutz : the index definition is part of the CREATE TABLE. In any case, it was added after all data was inserted with alter table eventlog add index idx_moment (moment); Dec 3, 2019 at 15:21
  • I need to process this full table in an external program, sorted by timestamp. The only way to increase performance which I see is to create clustered (for InnoDB - primary) covering index where moment field is a prefix. But InnoDB do not allow AUTO_INCREMENT field to be not a prefix of PK, so this solution is not applicable. So simple covering index where moment field is a prefix seems to be the best solution.
    – Akina
    Dec 3, 2019 at 17:25

1 Answer 1

1

First, I have to ask what the client will do with 280M rows? If it is a web page, that much data will crash the user's machine.

Here are two things to speed up getting data from that query:

  • Change the indexes to these:

    PRIMARY KEY(moment, id),  -- to cluster the data in the order to be fetched
    INDEX(id)   -- to keep AUTO_INCREMENT happy.
    

What you have now will fetch all the data, then sort it by moment, before delivering even the first row. With the indexing change, the sort is gone, and the rest can be 'streamed' directly. But will the client take advantage of that? Well...

  • Change the fetching mechanism to receive the data in chunks instead of "all at once". (The details of this choice are buried in the API of how the client talks to MySQL. I only use the "all at once". But I never ask for 280M rows.)

Drawbacks of "chunking":

  • The entire query takes longer. (That is, more than 37 minutes.)
  • Other connections may be impacted by such a long-running query.

Consider this alternative: SELECT ... INTO OUTFILE ... and then process the resulting file as you might process a CSV file.

4
  • I changed the indexes to your suggestion (except making the index on id UNIQUE). This works very well: first row is returned after 0.02s (was 17min), and total query time is 25min (was 37min). Thanks! Dec 11, 2019 at 9:15
  • Regarding your question: the data is processed on another computer, not a web page :-) I already use the streaming/'chunking' API you mention (MySQL++' Query::use()). For your information: not streaming the results has drawbacks as well: the complete result set needs to be stored somewhere, which can cause storage or memory problems (e.g. PHP 5 runs out of memory if the result set is not streamed and larger than the memory limit). Streaming is also usually faster, since processing begins earlier :-) Dec 11, 2019 at 9:18
  • @TomasCreemers - The only benefit of UNIQUE(id) is to prevent you from explicitly inserting a duplitate auto_inc value. But "nobody" ever does that.
    – Rick James
    Dec 11, 2019 at 16:17
  • @TomasCreemers - The huge improvement in getting the first row is due to avoiding the sort.
    – Rick James
    Dec 11, 2019 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.