jaymc Posted February 19, 2007 Share Posted February 19, 2007 Check this I got from PHPMYADMIN, these are supposedly the weak areas of my tuning so far See attached.. Any ideas of how to tune the selected areas? By the way the only queries that use JOIN is my invision forum [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted February 19, 2007 Share Posted February 19, 2007 The only way to really handle these is to get a baseline, and monitor them over time... but the first few seem worrisome. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 19, 2007 Author Share Posted February 19, 2007 How to overcome the first few? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 19, 2007 Share Posted February 19, 2007 Hard to say without a look at the slow query log, but you shouldn't have very many queries that don't utilize any indexes. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 19, 2007 Author Share Posted February 19, 2007 Slow queries is useless A standard query will show up as a slow query if the query is executed when the servers load is high.. Therefor it gives no real insight Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 19, 2007 Author Share Posted February 19, 2007 Edit Here is the result of slow query log which has bee activated for 30 minutes # Time: 070219 22:18:07 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 25 Rows_examined: 19736 SELECT t.*, t.title as topic_title FROM ibf_topics t WHERE t.approved=1 AND t.state != 'link' AND t.forum_id IN(4,40,43,72,14,6,15,21,20,10,7,12,52,37,16,17,18,23,61,62,63,64,66,67,65,46,51,50,49,47,48) AND t.last_post > 1171859372 ORDER BY t.last_post DESC LIMIT 0,25; # Time: 070219 22:20:35 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 648851 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171835374',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 22:28:20 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 18277 Rows_examined: 73103 SELECT m.id, m.members_display_name, m.mgroup, m.last_activity FROM ibf_members m LEFT JOIN ibf_groups g ON ( m.mgroup=g.g_id ) WHERE -172800 ORDER BY g.g_display ASC, m.members_display_name ASC; All are a result from my invision forum which has 18,000 members and 700,000 posts I know thats a lot but I also know MYSQL is capable of handling masses of data? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 20, 2007 Share Posted February 20, 2007 Slow queries is useless A standard query will show up as a slow query if the query is executed when the servers load is high.. Therefor it gives no real insight I did say you needed baseline first... As for the queries... I'd need to see the EXPLAIN for each one; but it "feels" like you're examining too many rows. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 20, 2007 Author Share Posted February 20, 2007 Here are some more slow queries Suprised a lot of them are coming from my invision forum, I thought they took quite some time optimizing there DB structure and queries # Time: 070219 23:03:59 # User@Host: root[root] @ localhost [] # Query_time: 11 Lock_time: 0 Rows_sent: 18279 Rows_examined: 73111 SELECT m.id, m.members_display_name, m.mgroup, m.last_activity FROM ibf_members m LEFT JOIN ibf_groups g ON ( m.mgroup=g.g_id ) WHERE -172800 ORDER BY g.g_display ASC, m.members_display_name ASC; # Time: 070219 23:04:00 # User@Host: root[root] @ localhost [] # Query_time: 11 Lock_time: 0 Rows_sent: 18279 Rows_examined: 73111 SELECT m.id, m.members_display_name, m.mgroup, m.last_activity FROM ibf_members m LEFT JOIN ibf_groups g ON ( m.mgroup=g.g_id ) WHERE -172800 ORDER BY g.g_display ASC, m.members_display_name ASC; # Time: 070219 23:04:01 # User@Host: root[root] @ localhost [] # Query_time: 18 Lock_time: 0 Rows_sent: 1 Rows_examined: 648904 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171847734',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:04:03 # User@Host: root[root] @ localhost [] # Query_time: 18 Lock_time: 0 Rows_sent: 1 Rows_examined: 20690 SELECT COUNT(*) AS total FROM ibf_topics WHERE start_date >= '1171843200'; # Time: 070219 23:04:04 # User@Host: root[root] @ localhost [] # Query_time: 21 Lock_time: 0 Rows_sent: 1 Rows_examined: 648904 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171847734',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:19:43 # User@Host: jaydio_jaydio[jaydio_jaydio] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 18 Rows_examined: 107496 use jaydio_jaydio; SELECT `ID` FROM `messages` WHERE `FROM` = 'louishansen' AND `READ` = '0'; # User@Host: jaydio_jaydio[jaydio_jaydio] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 2 Rows_examined: 107496 SELECT `ID` FROM `messages` WHERE `FROM` = 'munchngaz' AND `READ` = '0'; # Time: 070219 23:20:04 # User@Host: root[root] @ localhost [] # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 648913 use jaydio_forum; SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:30:04 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 648917 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171850194',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:34:50 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 648919 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171915400',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:38:20 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 648921 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171880377',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:44:25 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 5 Rows_examined: 19723 SELECT t.*, m.mgroup FROM ibf_topics t LEFT JOIN ibf_members m ON (m.id=t.last_poster_id) WHERE t.forum_id IN (0,4,40,43,72,14,6,15,21,20,10,7,12,52,37,16,17,18,23,61,62,63,64,66,67,65,46,51,50,49,47,48)and approved=1 ORDER BY t.last_post DESC LIMIT 0,5; # Time: 070219 23:44:30 # User@Host: jaydio_jaydio[jaydio_jaydio] @ localhost [] # Query_time: 12 Lock_time: 0 Rows_sent: 39 Rows_examined: 107607 use jaydio_jaydio; SELECT `ID` FROM `messages` WHERE `FROM` = 'louishansen' AND `READ` = '0'; # Time: 070219 23:44:32 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 648928 use jaydio_forum; SELECT COUNT(*) AS total, SUM(IF(post_date > '1171903893',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # User@Host: root[root] @ localhost [] # Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 648928 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171880377',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:44:34 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 648928 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171910934',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:45:05 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 648928 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:45:23 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 20691 SELECT COUNT(*) AS total FROM ibf_topics WHERE start_date >= '1171843200'; # Time: 070219 23:50:48 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 648940 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171912643',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070219 23:50:52 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 488 Rows_examined: 649463 SELECT p.pid, p.queued, t.approved, t.forum_id FROM ibf_posts p LEFT JOIN ibf_topics t on (t.tid=p.topic_id) WHERE t.forum_id IN (4,40,43,72,14,6,15,21,20,10,7,12,52,37,16,17,18,23,61,62,63,64,66,67,65,46,51,50,49,47,48) AND p.queued=0 AND ( LOWER(p.post) LIKE '%stephanie%' ); # Time: 070220 0:03:19 # User@Host: root[root] @ localhost [] # Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 648958 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171912231',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:10:08 # User@Host: root[root] @ localhost [] # Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 648967 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171915400',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 648967 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171912231',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:20:33 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 648981 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171916523',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:20:35 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 648981 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:20:36 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 648981 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171915400',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:32:11 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 648989 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171912643',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:32:12 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 648989 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171901758',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:34:50 # User@Host: root[root] @ localhost [] # Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 648992 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171909435',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:34:54 # User@Host: root[root] @ localhost [] # Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 648992 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 0:36:07 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 648995 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171901758',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 1:10:31 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 649044 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171917422',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 1:10:32 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 649044 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171913038',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 5:00:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108090 Rows_examined: 108090 use jaydio_jaydio; SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:02:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108090 Rows_examined: 108090 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:04:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108090 Rows_examined: 108090 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:10:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:11:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:13:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:15:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:18:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:19:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108091 Rows_examined: 108091 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:21:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108092 Rows_examined: 108092 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:22:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:23:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108085 Rows_examined: 108085 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:25:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108085 Rows_examined: 108085 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:26:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108085 Rows_examined: 108085 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:27:19 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 452858 Rows_examined: 452858 SELECT /*!40001 SQL_NO_CACHE */ * FROM `viewedpro`; # Time: 070220 5:29:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108085 Rows_examined: 108085 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:32:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:34:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:40:19 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 452862 Rows_examined: 452862 SELECT /*!40001 SQL_NO_CACHE */ * FROM `viewedpro`; # Time: 070220 5:41:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:42:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:43:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:45:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108086 Rows_examined: 108086 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:46:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 108862 Rows_examined: 108862 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:48:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 110976 Rows_examined: 110976 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:49:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 112084 Rows_examined: 112084 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:50:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 113174 Rows_examined: 113174 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:51:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 114317 Rows_examined: 114317 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:52:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 115523 Rows_examined: 115523 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:54:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 117883 Rows_examined: 117883 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:55:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 118971 Rows_examined: 118971 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:56:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 118971 Rows_examined: 118971 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:57:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 118971 Rows_examined: 118971 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:58:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 118971 Rows_examined: 118971 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 5:59:10 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 118971 Rows_examined: 118971 SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`; # Time: 070220 6:00:47 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 use jaydio_forum; SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:01:06 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:01:48 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:02:50 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:03:08 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:03:49 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:04:51 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:05:53 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:06:55 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:07:57 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:08:59 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:10:02 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:11:04 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:12:08 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:13:11 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:14:12 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:15:19 # User@Host: root[root] @ localhost [] # Query_time: 41 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:15:40 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:15:58 # User@Host: root[root] @ localhost [] # Query_time: 44 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:17:01 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:18:03 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:19:05 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:20:07 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:21:09 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:22:10 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:23:12 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:24:13 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:25:19 # User@Host: root[root] @ localhost [] # Query_time: 41 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:25:35 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 41053 Rows_examined: 41053 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topic_markers`; # Time: 070220 6:25:59 # User@Host: root[root] @ localhost [] # Query_time: 45 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:26:15 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 41053 Rows_examined: 41053 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topic_markers`; # Time: 070220 6:26:21 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:26:55 # User@Host: root[root] @ localhost [] # Query_time: 41 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:27:09 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 41053 Rows_examined: 41053 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topic_markers`; # Time: 070220 6:27:15 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:27:54 # User@Host: root[root] @ localhost [] # Query_time: 41 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:28:56 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:29:58 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:31:00 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:32:02 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:33:03 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:34:05 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:35:06 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:36:08 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:37:10 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:38:12 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:39:13 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:40:15 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:41:20 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:41:44 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 649097 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 6:41:45 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 649097 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 6:42:26 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:42:50 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 5 Rows_examined: 19727 SELECT t.*, m.mgroup FROM ibf_topics t LEFT JOIN ibf_members m ON (m.id=t.last_poster_id) WHERE t.forum_id IN (0,4,40,43,72,14,6,15,21,20,10,7,12,52,37,16,17,18,23,61,62,63,64,66,67,65,46,51,50,49,47,48)and approved=1 ORDER BY t.last_post DESC LIMIT 0,5; # Time: 070220 6:43:43 # User@Host: root[root] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:43:58 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 41053 Rows_examined: 41053 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topic_markers`; # Time: 070220 6:44:03 # User@Host: root[root] @ localhost [] # Query_time: 50 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:44:04 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:45:06 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:46:12 # User@Host: root[root] @ localhost [] # Query_time: 40 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:46:33 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:46:57 # User@Host: root[root] @ localhost [] # Query_time: 43 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:48:00 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:48:21 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:48:57 # User@Host: root[root] @ localhost [] # Query_time: 42 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:49:58 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:51:00 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:52:03 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:53:04 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:54:07 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:54:28 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:54:57 # User@Host: root[root] @ localhost [] # Query_time: 43 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:55:18 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 20698 Rows_examined: 20698 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_topics`; # Time: 070220 6:55:55 # User@Host: root[root] @ localhost [] # Query_time: 42 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:56:56 # User@Host: root[root] @ localhost [] # Query_time: 35 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:58:00 # User@Host: root[root] @ localhost [] # Query_time: 36 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 6:59:03 # User@Host: root[root] @ localhost [] # Query_time: 37 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 7:00:07 # User@Host: root[root] @ localhost [] # Query_time: 38 Lock_time: 0 Rows_sent: 649097 Rows_examined: 649097 SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts`; # Time: 070220 10:57:52 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 649135 SELECT COUNT(*) AS total, SUM(IF(post_date > '',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 10:57:56 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 649135 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171918261',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 10:57:58 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 649135 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171959192',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 13:20:09 # User@Host: root[root] @ localhost [] # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 649236 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171969530',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 13:23:14 # User@Host: root[root] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 18294 Rows_examined: 73171 SELECT m.id, m.members_display_name, m.mgroup, m.last_activity FROM ibf_members m LEFT JOIN ibf_groups g ON ( m.mgroup=g.g_id ) WHERE -172800 ORDER BY g.g_display ASC, m.members_display_name ASC; # Time: 070220 13:23:17 # User@Host: root[root] @ localhost [] # Query_time: 11 Lock_time: 1 Rows_sent: 1 Rows_examined: 649240 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171887563',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 13:23:51 # User@Host: jaydio_jaydio[jaydio_jaydio] @ localhost [] # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 113957 use jaydio_jaydio; SELECT `ID` FROM `messages` WHERE `FROM` = 'oban' AND `READ` = '0'; # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 649240 use jaydio_forum; SELECT COUNT(*) AS total, SUM(IF(post_date > '1171959192',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 13:24:49 # User@Host: root[root] @ localhost [] # Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 649244 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171849150',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; # Time: 070220 13:24:50 # User@Host: root[root] @ localhost [] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 649244 SELECT COUNT(*) AS total, SUM(IF(post_date > '1171461509',1,0)) AS latest_posts FROM ibf_posts WHERE queued = 0; Also, what is overhead on the tables and what is the effect? On some tables I had 25MB of overhead.. optimizing removed that but will it cause significant problems? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 20, 2007 Share Posted February 20, 2007 Like I said, I can't guess why the queries are slow if i don't see the tables and explain output. Overhead is simply the result of removing rows without removing the spaces left by that data (a form of fragmentation)... it's just not ideal, but it's not likely the source of your issue. Though if you haven't run ANALYZE TABLES in a while, that would update your index key distribution, and could affect performance quite a bit. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.