[eluser]jedd[/eluser]
[quote author="cold_fusion" date="1260683921"]
First of all, my query runs twice (once for counting, once for limiting and displaying data):
Code:
SELECT * FROM _tbl_firme LEFT JOIN tbl_djelatnosti ON _tbl_firme.d_id = tbl_djelatnosti.d_id WHERE d_seo = 'informatika-elektronika-racunari-i-oprema' AND _f_grad = 'Sarajevo' AND f_vidljivo = 1 ORDER BY f_istaknuto DESC, _f_ime ASC
[/quote]
I
think that for a count call you put less stress on your DB if you do this:
Code:
SELECT COUNT(id) AS count FROM _tbl_firme LEFT JOIN tbl_djelatnosti ON _tbl_firme.d_id = tbl_djelatnosti.d_id WHERE d_seo = 'informatika-elektronika-racunari-i-oprema' AND _f_grad = 'Sarajevo' AND f_vidljivo = 1 ORDER BY f_istaknuto DESC, _f_ime ASC
- and then utilise
count. I recall reading that * or COUNT(*) is more expensive than COUNT of an indexed field, though you should do some research on this to be sure.
Your model code is a bit scary.
Rather than this:
Quote:Code:
if($getDjelatnost && $getDjelatnost != 'Sve')
$append = " d_seo = '$getDjelatnost' AND ";
else $append = "";
$qry .= $append;
if($getGrad && $getGrad != 'Svi')
$append = " _f_grad = '$getGrad' AND ";
else $append = "";
$qry .= $append;
- you can just do this:
Code:
if($getDjelatnost && $getDjelatnost != 'Sve')
$qry .= " d_seo = '$getDjelatnost' AND ";
if($getGrad && $getGrad != 'Svi')
$qry .= " _f_grad = '$getGrad' AND ";
With a ternary operator you could even get that down to a single line per conditional.
Quote:But, what i want more than preventing the query run twice (i know many people here have the same problem),
I don't think there's a way around this - you need to do a select that includes a count() against every qualifying record, and a separate select to pull in the specific records you want. Keep in mind that the DB will cache the first query, and if the relevant table doesn't change, then subsequent (identical) queries will be blindingly fast. Enjoy the thrill of the database engine's performance!