MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Ronald Brown
Date: August 10, 2015 12:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
979
August 10, 2015 12:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.