| Re: how to avoid strange errors when we reach to memory_limit? |
| by irmtfan on 2012/10/15 4:35:50 Quote:
NO. maybe i forgot to mention clearly in my last post but I have 500 error and memory limit with the simple query too. SELECT COUNT(*) FROM `bb_posts`; Now i reach to this conclusion that limiting the number of results by adding WHERE will solve the issue. for example i dont have error when count a user posts: SELECT COUNT(*) FROM `bb_posts` WHERE (uid = '1') But maybe im still wrong. I can give you more information about the table maybe it can help. Table bb_posts Size is about 39MB and Rows are about 174,000 also I dont have any issue when i run the count query in phpmyadmin. Quote:
you can find all tables in newbb module. le="color: #000000"><?php CREATE TABLE `bb_posts` ( `post_id` int(10) unsigned NOT NULL auto_increment, `pid` int(10) unsigned NOT NULL default '0', `topic_id` int(8) unsigned NOT NULL default '0', `forum_id` smallint(4) unsigned NOT NULL default '0', `post_time` int(10) unsigned NOT NULL default '0', `uid` int(10) unsigned NOT NULL default '0', `poster_name` varchar(255) NOT NULL default '', `poster_ip` int(11) NOT NULL default '0', `subject` varchar(255) NOT NULL default '', `icon` varchar(25) NOT NULL default '', `attachsig` tinyint(1) unsigned NOT NULL default '0', `approved` smallint(2) NOT NULL default '1', `post_karma` int(10) unsigned NOT NULL default '0', `attachment` text, `require_reply` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`post_id`), KEY `uid` (`uid`), KEY `pid` (`pid`), KEY `forum_id` (`forum_id`), KEY `topic_id` (`topic_id`), KEY `subject` (`subject`(40)), KEY `forumid_uid` (`forum_id`,`uid`), KEY `topicid_uid` (`topic_id`,`uid`), KEY `post_time` (`post_time`), KEY `forumid_approved_postid` (`forum_id`,`approved`,`post_id`), FULLTEXT KEY `search` (`subject`(64)) ) ENGINE=MyISAM;
|
| Re: how to avoid strange errors when we reach to memory_limit? |
| by zyspec on 2012/10/14 22:02:37 The thing that is taking the time is the 'WHERE' clause. I dont' think you'd have the problem if you just did the following: le="color: #000000"><?php SELECT COUNT(*) FROM `bb_posts`; Clearly that's not a good answer but a count of the number of rows without the WHERE clause should work... What keys do you have on the bb_posts table? |
| Re: how to avoid strange errors when we reach to memory_limit? |
| by irmtfan on 2012/10/14 5:11:53 Yes, i have the 500 error only in COUNT(*) when the number of results are large. eg: i have error 500 with this query: SELECT COUNT(*) FROM `bb_posts` WHERE (forum_id IN (1,2) AND approved = '1' ) but dont have when i set a limit. eg: post_time > 2011/01/01 SELECT COUNT(*) FROM `bb_posts` WHERE (forum_id IN (1,2) AND approved = '1' AND post_time > '1293858000') See Youself: http://www.jadoogaran.org/modules/newbb/viewpost.php?easiestml_lang=en My website have posts from 2002 until now. It seems we should set some limits like the post_time for count. Edit: one thing that prove the memory limit: when i change the limit to post_time > 2010/01/01 i dont have problem as a guest but have 500 error as a webmaster. when i change the limit to post_time > 2007/01/01 i have error 500 as a guest too. |
| Re: how to avoid strange errors when we reach to memory_limit? |
| by zyspec on 2012/10/13 13:35:59 irmtfan, Are you saying that if you use something like the code below (with just a COUNT) le="color: #000000"><?php SELECT COUNT(*) FROM <tableprefix>_bb_posts; exit(); that you will get the 500 error or only when you try and use XoopsPageNav() class? Using COUNT() should not be causing out of memory errors, it seems that there must be something else... |
| Re: how to avoid strange errors when we reach to memory_limit? |
| by irmtfan on 2012/10/13 6:29:55 Voltan thank you for reply. Quote:
I cannot understand the second issue. to get the item list we always use a limit so for example in the first page it is: LIMIT 0, 10 and for example for page 225 if we have 10 items per page we have: LIMIT 2240, 10 so what is the difference? but for count we could not use limit. Also while the count is really fast when the number of items are huge it will affect the website. IMO just count is the problem. Quote:
so the question is: how can we have a pagenav if we dont count all items? we cannot use the latest index in the table because maybe many post_id had been deleted. maybe we can use "static php variable" for count items? |
Search
Recent Posts
Who's Online
Donat-O-Meter
| Stats | |
| Goal: | $15.00 |
| Due Date: | Jul 31 |
| Gross Amount: | $0.00 |
| Net Balance: | $0.00 |
| Left to go: | $15.00 |
|
|