Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to check how the table structure of a certain table effects the performance of a delete query.

This is not a generalised question, I have a table, with currently 2.2 million records approximately, i want to run delete query, I want to see how InnoDB stores data, in my disk, so i can change the structure of the table.

Is there any tool or command to check the structure of the table.

CREATE TABLE `chat_server` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `host` varchar(100) NOT NULL,
  `is_active` int(10) unsigned NOT NULL,
  `number_of_clans` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE QUERY

DELETE c FROM `chat_history` c inner join ( select message_id from `chat_history` where     clan_id=1005 ORDER BY message_id DESC limit 30, 30000  ) x where c.`message_id` <= x.`message_id` AND c.clan_id = 1005

thanks in advance.

share|improve this question
    
Please show the table structure with SHOW CREATE TABLE tblname\G. Also, please show the DELETE query itself. –  RolandoMySQLDBA Nov 11 '13 at 15:54
    
What version of MySQL are you using? –  peterm Nov 13 '13 at 3:37

1 Answer 1

Procedure Analyse will be helpful command: http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

SELECT *
FROM `table_name`
PROCEDURE ANALYSE ( )
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.