MySql Table schema performance issue

Question

I have the following table in mysql database:

CREATE TABLE `chronologicallists` (
`sno` int(250) NOT NULL AUTO_INCREMENT,   
`empSno` int(11) NOT NULL,
`dateOfHearing` date DEFAULT NULL,
 `stageOfProceedings` text CHARACTER SET latin1,
 `isDecided` tinyint(1) DEFAULT '0',
`caseSno` int(11) NOT NULL,
`isCurrent` tinyint(1) DEFAULT '1',
`isOut` tinyint(1) DEFAULT '0',
 `isTransferred` tinyint(1) DEFAULT '0',
 `c_position` smallint(2) DEFAULT '1',
 `d_method` smallint(2) NOT NULL DEFAULT '0',
  `reason_for_delay` text CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`sno`)
  ) ENGINE=MyISAM AUTO_INCREMENT=274926 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This table is working very fine for last 2+ years but since 3 days, it stuck by querying an DML execution. After executing 3 to 4 DML queries, it start taking too much time to execute the same query? It seems like there is any other process getting attach itself to mysqld and thus mysql execution get hang.

I am on windows 2012 Server -2 64-bit with mysql version 5.6.14. Total Size of this Table is : 30 MB

Please guide me what to do to improve my queries performance ?

Simple query example:

select COUNT(sno) as total from chronologicallists;

What does `EXPLAIN select COUNT(sno) as total from chronologicallists;` show? — Mad Dog Tannen, Oct 23 '14 at 13:49
It says: id=1 Select Type = SImple Table = Null Type=Null Possible_keys = Null Key=null key_len = null ref=null rows= null Extra = Select tables optimized away — Abdul Rahman, Oct 23 '14 at 14:02

score 0 · Answer 1 · answered Oct 23 '14 at 18:54

0

Do a

OPTIMIZE TABLE chronologicallists

to update the index statistics and free unused disk-space.

answered Oct 23 '14 at 18:54

Benvorth

7,416
8
49
70

MySql Table schema performance issue

1 Answers1