0

I'm trying to make a filter which matches every record in the table to find ones which are within the range @min and @max. They're unxi timestamps if anyone was wondering.

It does work right now, but, without a doubt, it is extremely inefficient. I was wondering if anyone could spot any optimisations or improvements which could reduce the processing time/latency. Due to its nature (it's for reminders), I will be running the query every 2-3 seconds, which is all the more reason for it to be as efficient as possible :p

Thanks in advance

This is the result of the query

The table's field types

SET @min := 1655827728037;
SET @max := 1656001256902;

SELECT * FROM (SELECT `user` FROM reminders
    WHERE @min < mission AND mission <= @max OR
          @min < report AND report <= @max OR
          @min < challenge AND challenge <= @max OR
          @min < tower AND tower <= @max OR
          @min < training AND training <= @max OR
          @min < daily AND daily <= @max OR
          @min < weekly AND weekly <= @max OR
          @min < quest AND quest <= @max OR
          @min < vote AND vote <= @max) AS users
LEFT JOIN (SELECT `user`, mission FROM reminders WHERE @min < mission AND mission <= @max) AS mission USING (`user`)
LEFT JOIN (SELECT `user`, report FROM reminders WHERE @min < report AND report <= @max) AS report USING (`user`)
LEFT JOIN (SELECT `user`, challenge FROM reminders WHERE @min < challenge AND challenge <= @max) AS challenge USING (`user`)
LEFT JOIN (SELECT `user`, tower FROM reminders WHERE @min < tower AND tower <= @max) AS tower USING (`user`)
LEFT JOIN (SELECT `user`, training FROM reminders WHERE @min < training AND training <= @max) AS training USING (`user`)
LEFT JOIN (SELECT `user`, daily FROM reminders WHERE @min < daily AND daily <= @max) AS daily USING (`user`)
LEFT JOIN (SELECT `user`, weekly FROM reminders WHERE @min < weekly AND weekly <= @max) AS weekly USING (`user`)
LEFT JOIN (SELECT `user`, quest FROM reminders WHERE @min < quest AND quest <= @max) AS quest USING (`user`)
LEFT JOIN (SELECT `user`, vote FROM reminders WHERE @min < vote AND vote <= @max) AS vote USING (`user`);
1
  • Your data storing schema is de-normalized which is obviously bad practice. I'd recommend to normalize it. but, without a doubt, it is extremely inefficient Pivotting is efficient never. Perform this transformation on the client side using its reporting service.
    – Akina
    Jun 24, 2022 at 4:30

1 Answer 1

0
  • Use SHOW CREATE TABLE reminders to present the table.
  • Use text, not images to provide information to us.
  • Use a numeric datatype, perhaps BIGINT in your case, for a column that will be compared against numbers
  • Perhaps have a single timestamp column, not one per type.
  • Have one row per reminder, not clump them together

After you have done all those, come back for further critique.

Your Answer

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

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