Optimize select mysql

Question

can help me to optimize this query? Thanks for your support

SELECT  cs_annunci.ID, cs_utenti.ID, cs_annunci.Data, cs_annunci.Tipologia,
        cs_annunci.Foto1, cs_annunci.Titolo, cs_annunci.IDRazza,
        cs_annunci.Cucciolo, cs_utenti.Attivita, cs_annunci.IDRubrica,
        cs_annunci.IDSottorubrica, cs_annunci.Position, cs_annunci.Ora,
        cs_annunci.Prezzo, cs_annunci.Pedigree, cs_annunci.IDProvincia
    FROM  cs_annunci, cs_utenti, cs_regioni
    WHERE  cs_annunci.IDUtente = cs_utenti.ID
      AND  cs_annunci.IDRegione = cs_regioni.ID
      AND  cs_utenti.Sospeso = '0'
      AND  cs_annunci.Attiva = '1'
      AND  cs_utenti.Cancellato = '0'
    ORDER BY  cs_annunci.Position DESC, cs_annunci.Data DESC,
        cs_annunci.Ora DESC
    LIMIT  0, 20

EXPLAIN (.png)

Please provide CREATE TABLE statements for all relevant tables. — Strawberry
– Strawberry, Commented Oct 1, 2016 at 10:20

Strawberry · Accepted Answer · 2016-10-01 10:23:36Z

1

As an aside, I find this easier to read...

 SELECT a.ID
      , u.ID
      , a.Data
      , a.Tipologia
      , a.Foto1
      , a.Titolo
      , a.IDRazza
      , a.Cucciolo
      , u.Attivita
      , a.IDRubrica
      , a.IDSottorubrica
      , a.Position
      , a.Ora
      , a.Prezzo
      , a.Pedigree
      , a.IDProvincia
   FROM cs_annunci a
   JOIN cs_utenti u
     ON u.ID = a.IDUtente 
   JOIN cs_regioni r
     ON r.ID = a.IDRegione 
  WHERE u.Sospeso = 0
    AND a.Attiva = 1
    AND u.Cancellato = 0
  ORDER 
     BY a.Position DESC
      , a.Data DESC
      , a.Ora DESC
  LIMIT 0, 20

answered Oct 1, 2016 at 10:23

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Good -- advocate short aliases and JOIN...ON.

Rick James · Accepted Answer · 2016-10-02 19:50:06Z

0

This index is likely to helps with the speed:

INDEX(Attiva, Position, Data, Ora)

in exactly that order.

The query does not seem to be using cs_regioni except for verifying that a row exists; remove references to that table if possible.

Index Cookbook .

If you need to discuss this further, please provide SHOW CREATE TABLE so we can know the datatypes, indexes, and engines involved.

answered Oct 2, 2016 at 19:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Optimize select mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related