Re: Utilize maximum system resources for better performance

[Ronald Brown]

Rick James Wrote:
-------------------------------------------------------
> > SELECT count(*) as total_results FROM content
> WHERE content.enabled = 1 ;
>
> takes a long time unless it is in the Query cache.
> Keep in mind that _any_ change to `total_results`
> will lose that COUNT from the QC.

okie., i ddi some testing while inserting rows .

QC or or off it doesnt matter, the culprit is

SELECT count(*) as total_results FROM content WHERE content.enabled = 1

if i dont fire count query, everything is fast.

so while inserting rows i guess it has to calculate the total_results value all over again.

now i am gonna disable QC and create a new table with results count, and gona do cronjob for updating total_results .

> > mysqli_autocommit($con,FALSE);
> > update rows,
> > mysqli_commit($con);
>
> That works. Doing the update one row at a time is
> a lot slower than some form of batching.

will implement something batch updating

> I prefer BEGIN; do stuff; COMMIT; instead of using
> autocommit.
>
> (autocommit is local to your session.)

and will do bencharmking about LOAD DATA INFINE and your method for updating existing rows from content tbale from stackoverflow answer.

Thanks.