Loading...
 
Architecture / Installation

Architecture / Installation


Slow Query battle still raging

posts: 8311 Israel

Thanks to Sylvie some mysql queries were corrected but still i found a nasty one and can't find what is causing it...

Any fresh mind around ????

Catch on tw3

Report for slow logs: /var/log/mysql/mysql-slow.log
3.32k queries total, 193 unique
Sorted by 't_sum'
Grand Totals: Time 11 s, Lock 0 s, Rows sent 15.72k, Rows Examined 1.86M


______________________________________________________________________ 001 ___
Count         : 84  (2.53%)
Time          : 5 s total, 59.524 ms avg, 0 to 1 s max  (45.45%)
  95% of Time : 0 total, 0 avg, 0 to 0 max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 5 avg, 3 to 30 max  (2.49%)
Rows examined : 1.23k avg, 1.22k to 1.55k max  (5.55%)
Database      : maintiki
Users         : 
	yonitiki@localhost  : 100.00% (84) of query, 19.67% (653) of all users

Query abstract:
SELECT tiki_articles.*, tiki_article_types.use_ratings, tiki_article_types.show_pre_publ, tiki_article_types.show_post_expire, tiki_article_types.heading_only, tiki_article_types.allow_comments, tiki_article_types.show_image, tiki_article_types.show_avatar, tiki_article_types.show_author, tiki_article_types.show_pubdate, tiki_article_types.show_expdate, tiki_article_types.show_reads, tiki_article_types.show_size, tiki_article_types.show_topline, tiki_article_types.show_subtitle, tiki_article_types.show_linkto, tiki_article_types.show_image_caption, tiki_article_types.show_lang, tiki_article_types.creator_edit FROM tiki_articles, tiki_article_types WHERE tiki_articles.publishdate>=N AND (tiki_articles.publishdateN OR tiki_article_types.show_post_expire='S') AND tiki_articles.type = tiki_article_types.type ORDER BY publishdate DESC LIMIT N,N;

Query sample:
select `tiki_articles`.*,
			`tiki_article_types`.`use_ratings`,
			`tiki_article_types`.`show_pre_publ`,
			`tiki_article_types`.`show_post_expire`,
			`tiki_article_types`.`heading_only`,
			`tiki_article_types`.`allow_comments`,
			`tiki_article_types`.`show_image`,
			`tiki_article_types`.`show_avatar`,
			`tiki_article_types`.`show_author`,
			`tiki_article_types`.`show_pubdate`,
			`tiki_article_types`.`show_expdate`,
			`tiki_article_types`.`show_reads`,
			`tiki_article_types`.`show_size`,
			`tiki_article_types`.`show_topline`,
			`tiki_article_types`.`show_subtitle`,
			`tiki_article_types`.`show_linkto`,
			`tiki_article_types`.`show_image_caption`,
			`tiki_article_types`.`show_lang`,
			`tiki_article_types`.`creator_edit`
			 from `tiki_articles`, `tiki_article_types`
			 where `tiki_articles`.`publishDate`>=0 and (`tiki_articles`.`publishDate`1277782720 or `tiki_article_types`.`show_post_expire`='y')  and   `tiki_articles`.`type` = `tiki_article_types`.`type` order by `publishDate` desc LIMIT 0,3;