CODEWITHSUNDEEP
mysqloptimizationsubquery
sabansaulic
sabansaulic

Reputation: 459

query avoding subselect

I have this query and it's very slow. Can i write this query in other way by avoiding subselects for example?

Also i tried adding composite index on 

MyIndex(kategorije_id,izdvojen,izdvojen_kad,datum)

but it doesent use it when i explain this query so is there another index that i can use to speed up this query? 

SELECT artikli.datum AS brojx, artikli.izdvojen AS i, artikli.izdvojen_kad AS ii, artikli.name
FROM artikli
WHERE artikli.izbrisan =0
AND artikli.prodano !=3
AND artikli.zavrseno =0
AND artikli.od_id !=0
AND kategorije_id
IN ( 18 )
AND (
SELECT count( * )
FROM artikli_polja, polja
WHERE polja.id_kat = artikli.kategorije_id
AND artikli_polja.id_polja = polja.id
AND artikli_polja.id_artikal = artikli.id
AND polja.name = "godiste"
AND artikli_polja.valueInt >= "1993"
) >0
AND (
SELECT count( * )
FROM artikli_polja, polja
WHERE polja.id_kat = artikli.kategorije_id
AND artikli_polja.id_polja = polja.id
AND artikli_polja.id_artikal = artikli.id
AND polja.name = "godiste"
AND artikli_polja.valueInt <= "2000"
) >0
ORDER BY i DESC , ii DESC , brojx DESC
LIMIT 140 , 35

Upvotes: 0

Views: 80

Answers (2)

Lambs
Lambs

Reputation: 91

Try this query:

SELECT a.datum AS brojx, a.izdvojen AS i, a.izdvojen_kad AS ii, a.name 
FROM artikli a
WHERE a.izbrisan =0
AND a.prodano !=3
AND a.zavrseno =0
AND a.od_id !=0
AND a.kategorije_id = 18
AND EXISTS 
   (select 'X' 
   from artikli_polja ap,
   JOIN polja p
   ON ap.id_polja = p.id AND p.id_kat = a.kategorije_id
   AND ap.id_artikal = a.id
   AND p.name = 'godiste'
   AND ap.valueInt >= 1993 AND ap.valueInt <= 2000
   );

Please check.

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this query -

SELECT a.datum AS brojx, a.izdvojen AS i, a.izdvojen_kad AS ii, a.name FROM artikli a
  JOIN artikli_polja ap
    ON ap.id_artikal = a.id
  JOIN polja p
    ON ap.id_polja = p.id AND p.id_kat = a.kategorije_id
WHERE
  a.izbrisan =0
  AND a.prodano !=3
  AND a.zavrseno =0
  AND a.od_id !=0
  AND kategorije_id = 18
  AND p.name = 'godiste'
  AND ap.valueInt >= 1993 AND ap.valueInt <= 2000;

I have removed ORDER BY and LIMIT clauses from the query; try to work out this query firstly.

Upvotes: 1

Related Questions