11
zyspec
Re: how to avoid strange errors when we reach to memory_limit?
  • 2012/10/14 22:02

  • zyspec

  • Module Developer

  • Posts: 1093

  • Since: 2004/9/21


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:

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?

12
irmtfan
Re: how to avoid strange errors when we reach to memory_limit?
  • 2012/10/15 4:35

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


Quote:

Clearly that's not a good answer but a count of the number of rows without the WHERE clause should work...

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:

What keys do you have on the bb_posts table?

you can find all tables in newbb module.
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;

Login

Username:
Password:

Lost Password? Register now!

Who's Online

58 user(s) are online (26 user(s) are browsing Support Forums)


Members: 0


Guests: 58


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: May 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits