~ overflow ~

Problem:

A supposed plain query like:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id
order by pacote.id desc

takes 15s to run, only because of the order by, assuming that each of the involved tables have at least 100,000 entries and pacote.id is the primary key.

Solution:

I’ve searched a lot but there’s no way to optimize this, limits will not help due the `order by`, having this query un-merged in many others will get worst results, having it without join will get about the same processing time (a bit slower in long run).

So, the rest of options were quite bad:
1. Remove crap rows out the tables
2. Get new hardware?!

Although pacote.added will not match pacote.id 100%, it will match 99.9%, so why not use the bare sorting (without order by mysql will output the rows in primary key order) ?

This would require a little coding to have it right:

So we have the same query as above WITHOUT order by:

select
	pacote.*,
	users.id as uid,users.usr,
	group_concat(content.nm separator '|') as fnm,
	group_concat(historico.mid separator '|') as fmid
from
	pacote
	join historico on pacote.id=historico.pid
	join content on historico.mid=content.id
	join users on users.id=historico.uid
group by historico.pid,pacote.id

(0.1s avg)

now on php:

// some vars
define("_ipp",20); // items per page
$x['page']=0; // current page

// do the query (assuming that $sql is the query above)
$res=mysql_db_query($db,$sql);

// build an array with ALL results
for ($rez=array();$r=mysql_fetch_array($res,MYSQL_ASSOC);$rez[]=$r) { }

// cut the array from the end (note minus signal)
$rez=array_slice($rez,-(intval($x['page']+1)*_ipp),_ipp);
krsort($rez); // invert array, simulating order by's DESC

foreach ($rez as $val) {
    // do things
}

(4.25s avg)

So the php solution is far faster than the mysql order by, although it’s not accurate as, it really solved the issue.