We are running few benchmark queries against our test RDS instance for checking efficiency of partitioning, before adapting it to production, where there is no partitions presently.

We have few tables of time series data, and hence partitioning based on RANGE( unix_timestamp(event_time) ). Queries we run against sets of tables(partitioned and non-partitioned ones) which has millions of rows is taking too much time, which we are stuck at the moment.

We see from MySQL Workbench it's quite obviously the fetch time(network dependent) takes up the time. What we are concerned about is only the runtime duration of the queries we run. Is there any way we can skip the fetched results part and only get the runtime duration for respective queries?

Query Examples we try :

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59';

What we do for saving time is with limit the results with,

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59'
    Limit  0,1000000;

which won't match live scenarios.

(I have too many Comments for Comments...)

  • Please provide SHOW CREATE TABLE. There are many details left out of the Question.
  • Please provide EXPLAIN SELECT .... We need to discuss whether it is really doing any pruning.
  • How many rows are returned by those queries?
  • Are they realistic queries -- do you really need all columns; do you really need all rows (versus need to summarize them)?
  • Depending on the indexes, you may find that partitioning is slowing down the operation, not speeding it up.
  • Let's see the real queries.
  • The following avoids computing the end date, leap-years, etc. And it grabs the missing second.

.

      event_time >= '2018-01-01'
  AND event_time  < '2018-01-01' + INTERVAL 2 MONTH

In my experience,

  • The only purpose for PARTITION BY RANGE(event_time) is to purge old data.
  • Summary tables are the real 'fix' for performance problems in log tables.

Once you have addressed most of my comments, the title Question should be moot.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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