MySQL query takes too long:

SELECT winlogs.image, winlogs.winitem, winlogs.grade, winlogs.caseid, winlogs.status, winlogs.price, winlogs.id, lockeditems.tradeid, cases.image AS caseimg, cases.grade AS casegrade, items.preview FROM winlogs LEFT JOIN lockeditems ON lockeditems.asset = winlogs.asset LEFT JOIN cases ON winlogs.caseid = cases.caseid LEFT JOIN b_items ON b_items.asset = winlogs.asset WHERE winlogs.userid = '616632589' ORDER BY winlogs.time DESC LIMIT 1, 25;

In the tables: winlogs: 5500 rows 10 columns, there is text lockeditems: 2 rows 7 cases column: 50 rows 15 columns b_items: 1000 rows 12 columns, there is text userid = # user.

Initially, the query worked quickly, when the user in winlogs had only 100-150 lines for example. Now this user 616632589 has about 1300 lines of victories (in the winlogs table), so the request itself is triggered 3 seconds (measured via show profiles), when you open its profile, terrible brakes. Not to mention what will happen when the user has 10,000+ wins.

What can be thought up to speed up the execution of the request? Tables touch / combine will not work.

  • one
    Please execute the same command together with the EXPLAIN ( EXPLAIN SELECT... ) command and add the result, perhaps, this will make the situation clearer. - Ksenia

2 answers 2

Try adding an index to the winlogs.userid field. You can also add indices for all columns used in ON phrases.

For more detailed optimization, you can recommend reading the book "MySQL. Performance Optimization" (especially the fourth chapter - "Query Optimization").

  • one
    The author of the question has such ridiculous volumes of tables (well, if only a few zeros were lost in the number of 5500), it becomes doubtful that the problem is in the absence of indices. Although, of course, indexes - this is the first thing that comes to mind. - Yaant pm
  • @Yaant, yes, I agree. In order to be able to say something more precisely, you can try to look at the results of the same query with EXPLAIN . - Ksenia
  • thanks, I will try now. - mikevozovsky
  • @Ksenia performed with explain: clip2net.com/clip/m0/96d28-clip-13kb.png?nocache=1 , is everything bad?) - mikevozovsky
  • @Ksenia eventually did something like this: clip2net.com/clip/m0/72600-clip-14kb.png?nocache=1 , as a result, the execution time is no longer 3+ seconds, but ~ 0.007 seconds (marked 3 indices). - mikevozovsky

Add the composite index winlogs.userid, winlogs.time, because userid in where, and time in order. As a result, all records are viewed.