MySQL request is too slow.



  • MySQL request has been implemented for 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: 5,500 lines 10 columns, text lockeditems: 2 lines 7 cases: 50 ppm b_items: 1,000 lines 12 pillars, text userid = # user.

    The original request worked fast when the user had only 100-150 lines in winlogs. Now, this box 616632589 has about 1,300 lines of winning (in the table winlogs), so the request itself operates 3 seconds (measure through show profiles) when its profile opens, creepy brakes. I'm not talking about what happens when the user has 10000+ wins. ♪

    What can you think of speeding up the request? It's not gonna be possible to touch the tables.



  • Try adding a field index. winlogs.userid♪ Indices for all columns used in the phrases may also be added ON

    For more detailed optimization, you can recommend reading the "MySQL." Optimization of productivity (especially chapter 4 - Optimization of requests).


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2