Re: optimization needed

[Brent Baisley]

Basically, you can't, it's a limitation of the InnoDB format. If you 
change the table type to MyISAM, that query would be almost 
instantaneous. But you are probably using InnoDB for a reason, so you 
may be stuck if you want a record count.

On May 5, 2004, at 10:01 AM, Vladimir Romanovski wrote:

Hi All!

We use MySQL 3.23.54 on dual-processor DELL running ReadHat 8.0 .
Performance of operations with one of the tables is very  low .
For example :
mysql> select count(*) from protocol;
+----------+
| count(*) |
+----------+
|     2266 |
+----------+
1 row in set (28.93 sec)
The table is created with statement:

CREATE TABLE protocol
(
  id             int(8)            NOT NULL auto_increment,
  time_stamp     timestamp         ,
   source         char(32)          default NULL,
  system         char(32)          default NULL,
  severity       enum('Debug',
                      'Info',
                      'Warning',
                      'Error')     default 'Debug',
  area           enum('Technical',
                      'Business',
                      'Security')  default 'Technical',
  priority       enum('A',
                      'B',
                      'C')         default 'C',
  exchange       char(32)          default NULL,
  isin           char(32)          default NULL,
  login          char(32)          default NULL,
  group_name     char(100)         default NULL,
  text           text              default NULL,
  msg_id         char(32)          default NULL,
  PRIMARY KEY    (id),
  INDEX protocol_MsgId (msg_id),
  INDEX protocol_TimeStamp (time_stamp)
) TYPE=InnoDB;
"SHOW TABLE STATUS" shows following result:
Name                protocol
Type                InnoDB
Row_format          Dynamic
Rows                1750258
Avg_row_length      233
Data_length         409387008
Max_data_length     NULL
Index_length        60948480
Data_free           0
Auto_increment      3647628
Create_time         NULL       Update_time         NULL       
Check_time          NULL      Create_options     Comment             
InnoDB free: 648192 kB

How can we tune this table!

Thanks for your help!
Vladimir


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]