optimize mysql schema, need advice

Question

I was asked to help to check a table and improve performance. The table is a large table with a 2.000.000 rows and is growing fast. There are lot's of users who are using this table with a lot of update / insert and delete queries.

Maybe you can give me some good advice to improve performance and realibity

Here is the table definition:

CREATE TABLE `calculate` (
  `GROUP_LINE_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `GROUP_LINE_PARENT_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `MOEDER_LINE_CODE` BIGINT(250) NOT NULL DEFAULT '0',
  `CALC_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `GROUP_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `CODE` VARCHAR(250) DEFAULT NULL,
  `DESCRIPTION` VARCHAR(250) DEFAULT NULL,
  `RAW_AMOUNT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `AMOUNT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `UNIT` VARCHAR(100) DEFAULT NULL,
  `MEN_HOURS` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `PRICE_PER_UNIT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `CONTRACTOR_UNIT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `POSTS_PER_UNIT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `SORT_INDEX` BIGINT(250) NOT NULL DEFAULT '0',
  `FACTOR` DECIMAL(50,4) NOT NULL DEFAULT '0.0000',
  `FACTOR_TYPE` INT(2) NOT NULL DEFAULT '0',
  `ROUND_AT` DECIMAL(50,2) NOT NULL DEFAULT '0.00',
  `MATERIAL_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `MINIMUM` DECIMAL(50,2) NOT NULL DEFAULT '0.00',
  `LINE_TYPE` INT(1) NOT NULL DEFAULT '0',
  `ONDERDRUKT` INT(5) NOT NULL DEFAULT '0',
  `MARKED` INT(5) NOT NULL DEFAULT '0',
  `IS_TEXT` INT(5) NOT NULL DEFAULT '0',
  `BRUTO_PRICE` DECIMAL(20,2) NOT NULL DEFAULT '0.00',
  `AMOUNT_DISCOUNT` DECIMAL(20,3) NOT NULL DEFAULT '0.000',
  `FROM_CONSTRUCTOR` INT(5) NOT NULL DEFAULT '0',
  `CHANGE_DATE` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `BEREKENING_VALUE` INT(5) NOT NULL DEFAULT '0',
  `MAATVOERING_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `KOZIJN_CALC_ID` BIGINT(250) NOT NULL DEFAULT '0',
  `IS_KOZIJN_CALC_TOTALS` INT(5) NOT NULL DEFAULT '0',
  `EAN_CODE` VARCHAR(150) DEFAULT NULL,
  `UURLOON_ID` BIGINT(20) NOT NULL DEFAULT '0',
  `ORG_PRICE_PER_UNIT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `ORG_CONTRACTOR_UNIT` DECIMAL(50,3) NOT NULL DEFAULT '0.000',
  `BTWCode` INT(5) NOT NULL DEFAULT '0',
  `IS_CONTROLE_GETAL` INT(5) NOT NULL DEFAULT '0',
  `AttentieRegel` INT(5) NOT NULL DEFAULT '0',
  `KozijnSelectionRowId` BIGINT(250) NOT NULL DEFAULT '0',
  `OfferteTekst` TEXT,
  `VerliesFactor` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`GROUP_LINE_ID`),
  KEY `GROUP_LINE_PARENT_ID` (`GROUP_LINE_PARENT_ID`),
  KEY `MOEDER_LINE_CODE` (`MOEDER_LINE_CODE`),
  KEY `CALC_ID` (`CALC_ID`),
  KEY `GROUP_ID` (`GROUP_ID`),
  KEY `MATERIAL_ID` (`MATERIAL_ID`),
  KEY `MAATVOERING_ID` (`MAATVOERING_ID`),
  KEY `KOZIJN_CALC_ID` (`KOZIJN_CALC_ID`),
  KEY `IS_KOZIJN_CALC_TOTALS` (`IS_KOZIJN_CALC_TOTALS`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In my opinion the

change table engine to InnoDB
bigint(250) can be changed to int(10) of bigint(10)?

Please give me some advice

Because int is 4 bits and bigint is 8 bits I guess int(4) and bigint(8) are the maximum values there is? — Ruutert

vidyadhar vidyadhar · Accepted Answer · 2013-01-18T10:46:04

yes you are right..

1.remember that a primary key in a table is assigned at the last of every other key so keep your primary key as less size as possible i think int is sufficient it can handle up to 2 billion records and big int is not needed

2.change key_buffer_size (up to 25% of your memory) if your server has many Myisam or only myisam table you can increase it up to 60-70% try manual chache

SET GLOBAL keycache1.key_buffer_size=256*1024;
CACHE INDEX t1,t2 IN keycache1;
LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

(The IGNORE LEAVES modifier causes only blocks for the nonleaf nodes of the index to be reloaded)

3.As you mentioned your table is growing faster it is better to partition the table which will improve the performance

4.Perform table maintenance tasks like analyse the table frequently which will update the indexing,optimize the table and check it and repair it if there are any errors (optimize table frequently because there are many deletes as you said)

5.if you don't want to change the engine then turn on delay_key_write variable (specific to myisam) which makes the keys to be updated after table closed

6.Run procedure analyse() which suggest you the best data types

7.Create full text indexes to take advontage of myisam if possible and only if it is useful

8.Examine your Query cache (set the Query cache limit to on demand ) and make slow query log is actvated

9.Once examine ( and rewrite if needed ) all the queries using the table

finally if you want to change engine change your table storage engine to innodb and increase the innodb_buffer_pool_size it may help you little bit

if number of accesses on the table are more then better to shift to innodb because myisam will implement table level locking due to which some queries are not logged in slow query log ( the initial time required to aquire the lock is not treated in execution time in MySQL )

optimize mysql schema, need advice

2 Answers