1

I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database.

The performance seems slow and inconsistent; sometimes it will return a single timeslice and take 0.5 seconds, sometimes it will return thousands and take 0.0002 seconds.

SELECT  count(*), time_slice
    FROM  logs
    WHERE  deployment_id = 123
      and  type="FOO"
      and  received_at BETWEEN "2019-04-01 00:00:00" AND "2019-04-30 23:59:59"
    GROUP BY  time_slice 
  • timeslice is a VARCHAR(18)
  • deployment_id is an INT(10)
  • type is a VARCHAR(16)
  • received_at is a DATETIME

I've created an index on each of those columns, which helps. It's still very inconsistent.

EXPLAIN SELECT tells me that it's using the deployment_id and type indexes.

Alternatively, is there a better way to bin lines on a 15 minute interval?

1

Most likely, the EXPLAIN plan is probably stating that the query will perform an index merge. That's when two indexes are searched and the intersection between them yields the results.

SUGGESTION

You can probably achieve slight better performance by creating one index as follows:

ALTER TABLE logs ADD INDEX (deployment_id,type,received_at,time_slice);

This will read from the index only to generate results. It should not have to read from the table. The reason ? Every column in the query is in the index.

TRADEOFF

This will make the table bigger but can improve read speed.

If the performance is the same after creating the index, you can probably do without the index at this time.

GIVE IT A TRY !!!

  • 1
    Table size is irrelevant for this, will give it a try. – evandentremont Jul 8 at 19:25
  • @evandentremont Could you share the results of this suggestion by Rolando? – Wilson Hauck Jul 18 at 22:08
0

As already mentioned, a particular 4-column index will help performance.

Table size is not totally irrelevant. Probably the variations in performance comes from sometimes finding the data in cache (the buffer_pool), sometimes not. I/O is the biggest component in performance.

Let's look at ways to shrink the table:

  • timeslice is a VARCHAR(18) -- Sounds like that could be shrunken to a 5-byte DATETIME that has been truncated to 15-minute boundaries.
  • deployment_id is an INT(10) -- How many distinct values will there be? INT occupies 4 bytes. TINYINT UNSIGNED takes only 1 byte and allows for values of 0..255.
  • type is a VARCHAR(16) -- How few values? Normalize it and replace it by a 1-byte TINYINT UNSIGNED.
  • received_at is a DATETIME -- (no change)

Those changes might triple the speed of the I/O-bound queries.

  • timeslice has the week built in for pulling weekly reports from. deployment_id there are 400ish right now. that could be smaller I suppose. type is arbitrary text. For example "BILLING" or "LOGGING" though they could be made into integers with a lookup table. – evandentremont Jul 9 at 15:42

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.