Slow Query Log Entries - UBB.threads PHP Forum Software Community

You are not logged in. [Log In] UBB.threads PHP Forum Software Community » Forums » UBB.threads 7 Discussion » General Assistance (How Do I?) » Slow Query Log Entries

Site Links

Home
Features
Documentation
Pricing & Order
Members Area
Support Options
UBBDev.com
UBBWiki.com

Top Posters (30 Days)

Ruben	49
DennyP	24
Gizmo	23
Dunny	18
SteveS	14
AllenAyres	12
dbremer	10
SD	9
drkknght00	9
driv	8

Latest Photos

Basildon Town Centre looking from the rounderbout

Topic Options

#175650 - 01/26/07 11:33 AM

Slow Query Log Entries

BellaOnline Offline

addict

Registered: 06/12/06
Posts: 515

Here are the slow query log entries, any hope of cleaning these up?

---------

# Query_time: 16 Lock_time: 0 Rows_sent: 200 Rows_examined: 289717
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('folding paper' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('folding paper' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 070125 19:00:56
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 17 Lock_time: 0 Rows_sent: 200 Rows_examined: 289763
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('christmas star' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('christmas star' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 070125 19:07:33
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 16 Lock_time: 0 Rows_sent: 0 Rows_examined: 289271
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('origarmi' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('origarmi' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 070125 19:28:14
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 16 Lock_time: 0 Rows_sent: 200 Rows_examined: 289777
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('crafts' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('crafts' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 070125 22:20:27
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 16 Lock_time: 0 Rows_sent: 33 Rows_examined: 289353
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','252','253','254','255','256','257','258','259','260','261','262','263','264','265','266','267','268','269','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','367','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('mosaic' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('mosaic' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt, Version: 4.1.16-nt-log. started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 070126 8:41:45
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 47 Lock_time: 0 Rows_sent: 200 Rows_examined: 289750
use ubbt7;
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('free' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('free' IN BOOLEAN MODE))

ORDER BY t1.POST_POSTED_TIME DESC
LIMIT 200;
# Time: 070126 9:08:33
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]
# Query_time: 17 Lock_time: 0 Rows_sent: 200 Rows_examined: 289744
SELECT t1.POST_ID
FROM ubbt7_POSTS as t1,
ubbt7_TOPICS as t2
WHERE t1.POST_IS_APPROVED = '1'
AND t2.TOPIC_STATUS <> 'M'
AND t2.FORUM_ID IN ('1','2','3','4','5','6','7','8','9','11','12','13','14','15','16','17','18','19','20','21','22','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','395','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427')
AND t1.TOPIC_ID = t2.TOPIC_ID
AND (MATCH t1.POST_SUBJECT AGAINST ('baby name' IN BOOLEAN MODE)
OR MATCH t1.POST_DEFAULT_BODY AGAINST ('baby name' IN BOOLEAN MODE))

_________________________
Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums

Top

Express Hosting

Express Hosting "We are the official hosting company of UBB.threads. Ask us about our free migration services to migrate your UBB.threads installation."

#175652 - 01/26/07 11:35 AM

Re: Slow Query Log Entries [Re: BellaOnline]

BellaOnline Offline

addict

Registered: 06/12/06
Posts: 515

I.e. it seems the user is probably searching "all public forums". Can't you just do the query for form type = public, vs having to enumerate every single ID? String-matching text queries are slow enough to start with ...

_________________________
Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums

Top

#175653 - 01/26/07 11:36 AM

Re: Slow Query Log Entries [Re: BellaOnline]

Rick

Post-a-holic

Registered: 06/04/06
Posts: 10164
Loc: Aberdeen, WA

Those are all search queries that can take the longest depending on how wide of a search range you specify as the max search range. What do you have for your max search range?

Top

#175659 - 01/26/07 12:36 PM

Re: Slow Query Log Entries [Re: BellaOnline]

Gizmo

Registered: 06/05/06
Posts: 14994
Loc: Portland, OR; USA

Originally Posted By: BellaOnline

Can't you just do the query for form type = public, vs having to enumerate every single ID? String-matching text queries are slow enough to start with ...

No, if you have "private" forums on your site these would get included.

_________________________
Forums: UGN Security & VNC Web Design & Development
UBB.Threads: UBB.Wiki, My UBBSkins, UBB.Sitemaps
Longtime UBB Supporter, UBB Beta Tester & Resident Post-A-Holic.
UBB Modifications, Styling, Coding Services, Disaster Recovery, and more!

Top

Previous Topic

Index

Next Topic

Hop to:

Moderator: AllenAyres, Harold, Ian, Ron M

Shout Box

May
Su	M	Tu	W	Th	F	Sa
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Today's Birthdays

No Birthdays