How can I optimize this query?

SELECT InstanceID, max(Slot) as slot, (SELECT reseller FROM `mod_TeamSpeak3Statistics_instance` WHERE`InstanceID` = mod_TeamSpeak3Statistics_data.InstanceID) as 'reseller', (SELECT partner FROM `mod_TeamSpeak3Statistics_instance` WHERE`InstanceID` = mod_TeamSpeak3Statistics_data.InstanceID) as 'partner', `Timestamp` AS 'date' FROM `mod_TeamSpeak3Statistics_data` WHERE DATE_FORMAT(FROM_UNIXTIME(`Timestamp`), '%Y-%m-%d') ='2016-12-18' GROUP by DATE_FORMAT(FROM_UNIXTIME(`Timestamp`), '%Y-%m-%d'),InstanceID ORDER BY InstanceID ASC

Profiling: Request profiling

  • Do you think that your request in one line of 500+ characters is readable?) - Denis
  • Try to make subqueries from the Select section in From so that they are not executed for each record. - androschuk
  • What is there to try? make sure to. - Akina

3 answers 3

  1. Remove subqueries

  2. Minimize the use of fields in expressions. (I don’t know how much MySQL differs from MS SQL here, but it’s logical that it’s easier to calculate a pair of constants than to apply a transformation to each row)

    SELECT InstanceID, max (Slot) as slot,
    i.reseller,
    i.partner,
    Timestamp AS 'date'
    FROM mod_TeamSpeak3Statistics_data d
    JOIN mod_TeamSpeak3Statistics_instance i ON i.InstanceID = d.InstanceID
    WHERE Timestamp BETWEEN UNIX_TIMESTAMP ('2016-12-18 00:00:00') AND UNIX_TIMESTAMP ('2016-12-18 23:59:59')
    GROUP by DATE_FORMAT (FROM_UNIXTIME ( Timestamp ), '% Y-% m-% d'), InstanceID
    ORDER BY InstanceID ASC

  • Thank you, "Copying To Tmp Table" on average already takes 350 ms. In fact, the savings are 200 ms already, how else can you optimize the query? The selection comes from a table in which statistics are stored, about 25 thousand records are obtained per day - Artem Ivanko
  • @ArtemIvanko swap fields in the group by, so that InstanceID comes first. Perhaps this way you can avoid unnecessary re-sorting. In general, bring the current execution plan to the question ( explain ваш-запрос ) - Mike
  • If you swap fields in the group by, so that InstanceID comes first, the query execution time is increased by 100ms execution plan: image.prntscr.com/image/2c3688c3a9194a54a2a28e90c0cd4924.png - Artem Ivanko
 SELECT md.InstanceID, max(md.slot) as 'slot', mi.reseller as 'reseller', mi.partner as 'partner', md.Timestamp AS 'date' FROM mod_TeamSpeak3Statistics_data md, mod_TeamSpeak3Statistics_instance mi WHERE DATE_FORMAT(FROM_UNIXTIME(md.Timestamp), '%Y-%m-%d') ='2016-12-18' AND md.InstanceID = mi.InstanceID GROUP by 5, 1 ORDER BY 1 ASC

Index InstanceID if it is not PK in mod_TeamSpeak3Statistics_data Attach a query plan that would help you optimize the query correctly.

  • Your version is slower than the Denis version of the query plan: image.prntscr.com/image/2c3688c3a9194a54a2a28e90c0cd4924.png - Artem Ivanko
  • You are going through all the values. I understand the sample should be by day and the date in the current table you have with time. There is a suggestion to add either an index table (a table with an InstanceID and an indexDate link by id), or add a ymd field to the current table. - PolyakovAO
  • Fill either with your software or Triger ... This will speed up the request and mine, and the request Denis. It is also necessary to do this, since you wrote about 25k records every day, and sooner or later you will return to the question of optimization. - PolyakovAO
  • The expression where somefunc(fieldname) = const is a guaranteed fullscan. Because you obviously built the index not by somefunc(fieldname) but by fieldname . Use the normal where fieldname between const and const + interval '1 day' . PS: order by 1 - deprecated. - Small
  • @ Small Strange how it turns out ... Even with such a request, everything is done long enough ... prntscr.com/dlhkcn - Artem Ivanko

UDP after deleting the InstanceID index for the mod_TeamSpeak3Statistics_data table this query is executed in 0.1990 seconds (0.2300 seconds on average)

 SELECT d.InstanceID, max(d.Slot) as slot, max(d.Online) as Online, i.reseller, i.partner, Timestamp FROM mod_TeamSpeak3Statistics_data d JOIN mod_TeamSpeak3Statistics_instance i ON i.InstanceID = d.InstanceID WHERE Timestamp between UNIX_TIMESTAMP('2016-12-20 00:00:00') and UNIX_TIMESTAMP('2016-12-20 23:59:59') GROUP by DATE_FORMAT(FROM_UNIXTIME(d.Timestamp), '%Y-%m-%d'), d.InstanceID ORDER BY 1 ASC

Request Plan: enter image description here

Profiling: enter image description here

What optimization options are there yet?