Re: MySQL hang after select count()

Abu Wed, 28 Nov 2001 18:35:52 -0800


After I replace with this DDL mysql back normaly, 
the load average 13 if i change max_connection=150 and table_cache=96 
using the second DDL.


mysqler expert please analyze this, i would know why.

the query just result total of record:
1st query:
+----------+
| COUNT(*) |
+----------+
|    35063 |
+----------+
1 row in set (1.58 sec)

2nd query:
+----------+
| COUNT(*) |
+----------+
|       40 |
+----------+
1 row in set (1.40 sec)


DDL query will mysql run normaly:

# MySQL dump 8.13
#
# Host: localhost    Database: udm
#--------------------------------------------------------
# Server version        3.23.36-log

#
# Table structure for table 'ACCOUNT'
#

CREATE TABLE ACCOUNT (
  ID int(11) NOT NULL auto_increment,
  STATUS int(3) NOT NULL default '160',
  NAME varchar(16) NOT NULL default '',
  PASSWD varchar(13) NOT NULL default '',
  DOMAIN int(11) NOT NULL default '0',
  CATEGORY int(11) NOT NULL default '0',
  KEY NAME (NAME,STATUS,DOMAIN),
  PRIMARY KEY  (ID),
  KEY name_2 (NAME),
  KEY domain (DOMAIN)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'ACL'
#

CREATE TABLE ACL (
  ID int(11) NOT NULL auto_increment,
  FUNC int(11) NOT NULL default '0',
  ACCOUNT int(11) NOT NULL default '0',
  KEY ACCOUNT (ACCOUNT),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'BUSINESS_TYPE'
#

CREATE TABLE BUSINESS_TYPE (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(128) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'CATEGORY'
#

CREATE TABLE CATEGORY (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  DESCRIPTION varchar(255) NOT NULL default '',
  IS_PUBLIC int(1) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'COUNTRY'
#

CREATE TABLE COUNTRY (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  CODE char(2) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DEFAULT_ACL'
#

CREATE TABLE DEFAULT_ACL (
  ID int(11) NOT NULL auto_increment,
  ROLE int(11) NOT NULL default '0',
  FUNC int(11) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DEFAULT_DOMAIN'
#

CREATE TABLE DEFAULT_DOMAIN (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DEFAULT_MENU'
#

CREATE TABLE DEFAULT_MENU (
  ID int(11) NOT NULL auto_increment,
  FUNC int(11) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DOMAIN'
#

CREATE TABLE DOMAIN (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(128) NOT NULL default '',
  STATUS int(3) NOT NULL default '160',
  CATEGORY int(11) NOT NULL default '0',
  PARENT int(11) NOT NULL default '0',
  IS_PUBLIC char(1) NOT NULL default 'N',
  KEY NAME (NAME),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DOMAIN_ADMIN'
#

CREATE TABLE DOMAIN_ADMIN (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  ADMIN int(11) NOT NULL default '0',
  MASTER int(4) NOT NULL default '0',
  KEY DOMAIN (DOMAIN,ADMIN),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'DOMAIN_TAIL'
#

CREATE TABLE DOMAIN_TAIL (
  ID int(3) NOT NULL auto_increment,
  LABEL varchar(10) NOT NULL default '',
  PRIMARY KEY  (ID),
  UNIQUE KEY LABEL (LABEL)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'EMAIL_ADDRESS'
#

CREATE TABLE EMAIL_ADDRESS (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  MAILBOX varchar(128) NOT NULL default '',
  PRIMARY KEY  (ID),
  UNIQUE KEY ACCOUNT (ACCOUNT,ADDRESS),
  KEY ADDRESS (ADDRESS)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'EMAIL_PREFERENCE'
#

CREATE TABLE EMAIL_PREFERENCE (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  HINT_QUESTION int(11) NOT NULL default '0',
  HINT_ANSWER varchar(128) NOT NULL default '',
  QUOTA int(11) NOT NULL default '3000',
  AUTO_FORWARD char(1) NOT NULL default 'N',
  POP_ENABLE char(1) NOT NULL default 'N',
  UNIQUE KEY ACCOUNT (ACCOUNT),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'FUNCTIONS'
#

CREATE TABLE FUNCTIONS (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  URL varchar(128) NOT NULL default '',
  HANDLER varchar(128) NOT NULL default '',
  DESCRIPTION varchar(255) NOT NULL default '',
  PARENT int(11) NOT NULL default '0',
  STATUS int(1) NOT NULL default '0',
  IS_MENU int(1) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'GENDER'
#

CREATE TABLE GENDER (
  CODE int(1) NOT NULL default '0',
  LABEL varchar(16) NOT NULL default '',
  PRIMARY KEY  (CODE)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'HINT_QUESTION'
#

CREATE TABLE HINT_QUESTION (
  ID int(11) NOT NULL auto_increment,
  QUESTION varchar(128) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'INTEREST'
#

CREATE TABLE INTEREST (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(64) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'OCCUPATION'
#

CREATE TABLE OCCUPATION (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(128) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'ORGANIZATION_PROFILE'
#

CREATE TABLE ORGANIZATION_PROFILE (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  ORGANIZATION_NAME varchar(128) NOT NULL default '',
  BUSSINESS_TYPE int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  PHONE varchar(32) NOT NULL default '',
  FAX varchar(32) NOT NULL default '',
  CITY varchar(32) NOT NULL default '',
  ZIP_CODE varchar(8) NOT NULL default '',
  COUNTRY int(11) NOT NULL default '0',
  HOMEPAGE varchar(128) NOT NULL default '',
  MEMBERS int(11) NOT NULL default '0',
  KEY ORGANIZATION_NAME (ORGANIZATION_NAME,DOMAIN),
  UNIQUE KEY DOMAIN (DOMAIN),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'ROLE'
#

CREATE TABLE ROLE (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(16) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'ROLE_ACCOUNT'
#

CREATE TABLE ROLE_ACCOUNT (
  ID int(11) NOT NULL auto_increment,
  ROLE int(11) default NULL,
  ACCOUNT int(11) default NULL,
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'SESSIONS'
#

CREATE TABLE SESSIONS (
  ID varchar(32) NOT NULL default '',
  A_SESSION mediumtext NOT NULL,
  LAST_ACCESS int(11) NOT NULL default '0',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'STATUS'
#

CREATE TABLE STATUS (
  ID int(3) NOT NULL default '0',
  NAME varchar(16) NOT NULL default '',
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'TABLE_REFERENCES'
#

CREATE TABLE TABLE_REFERENCES (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  UNIQUE KEY NAME (NAME),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;

#
# Table structure for table 'USER_PROFILE'
#

CREATE TABLE USER_PROFILE (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  REALNAME varchar(128) NOT NULL default '',
  BIRTHDAY date NOT NULL default '0000-00-00',
  GENDER int(1) NOT NULL default '0',
  OCCUPATION int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  PHONE varchar(32) NOT NULL default '',
  FAX varchar(32) NOT NULL default '',
  CITY varchar(32) NOT NULL default '',
  ZIP_CODE varchar(8) NOT NULL default '',
  COUNTRY int(11) NOT NULL default '0',
  MOBILE_PHONE varchar(32) NOT NULL default '',
  ALTERNATE_EMAIL varchar(128) NOT NULL default '',
  HOMEPAGE varchar(128) NOT NULL default '',
  INTEREST varchar(16) NOT NULL default '',
  KEY REALNAME (REALNAME,ACCOUNT),
  UNIQUE KEY ACCOUNT (ACCOUNT),
  PRIMARY KEY  (ID)
) TYPE=ISAM PACK_KEYS=1;


DDL will make hang the mysql and load average system=4-13:
------------------------------------------------------

> Total Record:
> ACCOUNT=23203
> DOMAIN=88
> STATUS=4
> USER_PROFILE=23202
> EMAIL_ADDRESS=23203
> DOMAIN_ADMIN=119


# MySQL dump 8.12
#
# Host: localhost    Database: udm
#--------------------------------------------------------
# Server version        3.23.32

#
# Table structure for table 'ACCOUNT'
#

DROP TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT (
  ID int(11) NOT NULL auto_increment,
  STATUS int(3) NOT NULL default '160',
  NAME varchar(16) NOT NULL default '',
  PASSWD varchar(13) NOT NULL default '',
  DOMAIN int(11) NOT NULL default '0',
  CATEGORY int(11) NOT NULL default '0',
  PRIMARY KEY (ID),
  UNIQUE KEY NAME(NAME)
) TYPE=MyISAM;

#
# Table structure for table 'ACL'
#

DROP TABLE IF EXISTS ACL;
CREATE TABLE ACL (
  ID int(11) NOT NULL auto_increment,
  FUNC int(11) NOT NULL default '0',
  ACCOUNT int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'BUSINESS_TYPE'
#

DROP TABLE IF EXISTS BUSINESS_TYPE;
CREATE TABLE BUSINESS_TYPE (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(128) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'CATEGORY'
#

DROP TABLE IF EXISTS CATEGORY;
CREATE TABLE CATEGORY (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  DESCRIPTION varchar(255) NOT NULL default '',
  IS_PUBLIC int(1) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'COUNTRY'
#

DROP TABLE IF EXISTS COUNTRY;
CREATE TABLE COUNTRY (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  CODE char(2) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'DEFAULT_ACL'
#

DROP TABLE IF EXISTS DEFAULT_ACL;
CREATE TABLE DEFAULT_ACL (
  ID int(11) NOT NULL auto_increment,
  ROLE int(11) NOT NULL default '0',
  FUNC int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'DEFAULT_DOMAIN'
#

DROP TABLE IF EXISTS DEFAULT_DOMAIN;
CREATE TABLE DEFAULT_DOMAIN (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'DEFAULT_MENU'
#

DROP TABLE IF EXISTS DEFAULT_MENU;
CREATE TABLE DEFAULT_MENU (
  ID int(11) NOT NULL auto_increment,
  FUNC int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'DOMAIN'
#

DROP TABLE IF EXISTS DOMAIN;
CREATE TABLE DOMAIN (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(128) NOT NULL default '',
  STATUS int(3) NOT NULL default '160',
  CATEGORY int(11) NOT NULL default '0',
  PARENT int(11) NOT NULL default '0',
  IS_PUBLIC char(1) NOT NULL default 'N',
  PRIMARY KEY (ID),
  UNIQUE KEY NAME(NAME)
) TYPE=MyISAM;

#
# Table structure for table 'DOMAIN_ADMIN'
#

DROP TABLE IF EXISTS DOMAIN_ADMIN;
CREATE TABLE DOMAIN_ADMIN (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  ADMIN int(11) NOT NULL default '0',
  MASTER int(4) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'DOMAIN_TAIL'
#

DROP TABLE IF EXISTS DOMAIN_TAIL;
CREATE TABLE DOMAIN_TAIL (
  ID int(3) NOT NULL auto_increment,
  LABEL varchar(10) NOT NULL default '',
  PRIMARY KEY (ID),
  UNIQUE KEY LABEL(LABEL)
) TYPE=MyISAM;

#
# Table structure for table 'EMAIL_ADDRESS'
#

DROP TABLE IF EXISTS EMAIL_ADDRESS;
CREATE TABLE EMAIL_ADDRESS (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  MAILBOX varchar(128) NOT NULL default '',
  PRIMARY KEY (ID),
  UNIQUE KEY (ADDRESS)
) TYPE=MyISAM;

#
# Table structure for table 'EMAIL_PREFERENCE'
#

DROP TABLE IF EXISTS EMAIL_PREFERENCE;
CREATE TABLE EMAIL_PREFERENCE (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  HINT_QUESTION int(11) NOT NULL default '0',
  HINT_ANSWER varchar(128) NOT NULL default '',
  QUOTA int(11) NOT NULL default '3000',
  AUTO_FORWARD char(1) NOT NULL default 'N',
  POP_ENABLE char(1) NOT NULL default 'N',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'FUNCTIONS'
#

DROP TABLE IF EXISTS FUNCTIONS;
CREATE TABLE FUNCTIONS (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL default '',
  URL varchar(128) NOT NULL default '',
  HANDLER varchar(128) NOT NULL default '',
  DESCRIPTION varchar(255) NOT NULL default '',
  PARENT int(11) NOT NULL default '0',
  STATUS int(1) NOT NULL default '0',
  IS_MENU int(1) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'GENDER'
#

DROP TABLE IF EXISTS GENDER;
CREATE TABLE GENDER (
  CODE int(1) NOT NULL default '0',
  LABEL varchar(16) NOT NULL default '',
  PRIMARY KEY (CODE)
) TYPE=MyISAM;

#
# Table structure for table 'HINT_QUESTION'
#

DROP TABLE IF EXISTS HINT_QUESTION;
CREATE TABLE HINT_QUESTION (
  ID int(11) NOT NULL auto_increment,
  QUESTION varchar(128) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'INTEREST'
#

DROP TABLE IF EXISTS INTEREST;
CREATE TABLE INTEREST (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(64) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'OCCUPATION'
#

DROP TABLE IF EXISTS OCCUPATION;
CREATE TABLE OCCUPATION (
  ID int(11) NOT NULL auto_increment,
  LABEL varchar(128) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'ORGANIZATION_PROFILE'
#

DROP TABLE IF EXISTS ORGANIZATION_PROFILE;
CREATE TABLE ORGANIZATION_PROFILE (
  ID int(11) NOT NULL auto_increment,
  DOMAIN int(11) NOT NULL default '0',
  ORGANIZATION_NAME varchar(128) NOT NULL default '',
  BUSSINESS_TYPE int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  PHONE varchar(32) NOT NULL default '',
  FAX varchar(32) NOT NULL default '',
  CITY varchar(32) NOT NULL default '',
  ZIP_CODE varchar(8) NOT NULL default '',
  COUNTRY int(11) NOT NULL default '0',
  HOMEPAGE varchar(128) NOT NULL default '',
  MEMBERS int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'ROLE'
#

DROP TABLE IF EXISTS ROLE;
CREATE TABLE ROLE (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(16) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'ROLE_ACCOUNT'
#

DROP TABLE IF EXISTS ROLE_ACCOUNT;
CREATE TABLE ROLE_ACCOUNT (
  ID int(11) NOT NULL auto_increment,
  ROLE int(11) default NULL,
  ACCOUNT int(11) default NULL,
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'SESSIONS'
#

DROP TABLE IF EXISTS SESSIONS;
CREATE TABLE SESSIONS (
  ID varchar(32) NOT NULL default '',
  A_SESSION mediumtext NOT NULL,
  LAST_ACCESS int(11) NOT NULL default '0',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'STATUS'
#

DROP TABLE IF EXISTS STATUS;
CREATE TABLE STATUS (
  ID int(3) NOT NULL default '0',
  NAME varchar(16) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Table structure for table 'USER_PROFILE'
#

DROP TABLE IF EXISTS USER_PROFILE;
CREATE TABLE USER_PROFILE (
  ID int(11) NOT NULL auto_increment,
  ACCOUNT int(11) NOT NULL default '0',
  REALNAME varchar(128) NOT NULL default '',
  BIRTHDAY date NOT NULL default '0000-00-00',
  GENDER int(1) NOT NULL default '0',
  OCCUPATION int(11) NOT NULL default '0',
  ADDRESS varchar(128) NOT NULL default '',
  PHONE varchar(32) NOT NULL default '',
  FAX varchar(32) NOT NULL default '',
  CITY varchar(32) NOT NULL default '',
  ZIP_CODE varchar(8) NOT NULL default '',
  COUNTRY int(11) NOT NULL default '0',
  MOBILE_PHONE varchar(32) NOT NULL default '',
  ALTERNATE_EMAIL varchar(128) NOT NULL default '',
  HOMEPAGE varchar(128) NOT NULL default '',
  INTEREST varchar(16) NOT NULL default '',
  PRIMARY KEY (ID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS TABLE_REFERENCES;
CREATE TABLE TABLE_REFERENCES (
  ID int(11) NOT NULL auto_increment,
  NAME varchar(64) NOT NULL,
  UNIQUE (NAME),
  PRIMARY KEY (ID)
);





Benjamin Pflugmann([EMAIL PROTECTED])@Wed, Nov 28, 2001 at 11:37:16PM +0100:
> Hi.
> 
> Please post the result of EXPLAIN for the two queries.
> 
> Bye,
> 
>       Benjamin.
-- 
   __   
  (oo)  Open Solution Provider visit http://www.trabas.com
 / \/ \ GnuPg public information         pub 1024/EBD26280 
 `V__V' A9A9 8F57 9E9D 14E3 05B4  3EDB C241 A313 EBD2 6280
Don't relax!  It's only your tension that's holding you together.

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL hang after select count()

Reply via email to