CODEWITHSUNDEEP
mysqlperformanceoptimizationindexingquery-optimization
Pavel Straka
Pavel Straka

Reputation: 427

MySQL table - performance, count of indexes

I have table with this structure:

CREATE TABLE `supplies` (
 `cycle_id` int(11) NOT NULL,
 `subject_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 `market_id` int(11) NOT NULL,
 `price` int(11) NOT NULL,
 `currency_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
 `offered_order` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`subject_id`,`market_id`,`cycle_id`,`price`),
 KEY `fk_supplies_subjects` (`subject_id`),
 KEY `fk_supplies_markets` (`market_id`),
 KEY `fk_supplies_currencies` (`currency_id`),
 CONSTRAINT `fk_supplies_currencies` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`currency_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_supplies_subjects` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_supplies_markets` FOREIGN KEY (`market_id`) REFERENCES `markets` (`market_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table has about 20,000 rows. I have problems with performance. I am logging SQL queries that are taking long time and from the log I can see:

  1. Slow INSERTs into table.

    INSERT INTO supplies VALUES (11, 'userName', 18, 30, 'pound', 20) ;

  2. Slow DELETEs from table

    DELETE FROM supplies WHERE cycle_id = 6 AND market_id = 18 AND subject_id =  'userName' ;

What could I improve?

I guess, changing the indexes would help, because the PRIMARY key is formed from 4 columns (and some are even varchars). But how should I change the indexes?

Thanks everyone.

Upvotes: 0

Views: 67

Answers (1)

André Lima
André Lima

Reputation: 141

You should create a primary key with only one field. This is a basic performance optimization

CREATE TABLE `supplies` (
 `supplies_id` int(11) NOT NULL AUTO_INCREMENT,
 `cycle_id` int(11) NOT NULL,
 `subject_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 `market_id` int(11) NOT NULL,
 `price` int(11) NOT NULL,
 `currency_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
 `offered_order` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`supplies_id`),
 INDEX `index` (`subject_id` ASC,`market_id` ASC,`cycle_id` ASC,`price` ASC))
 KEY `fk_supplies_subjects` (`subject_id`),
 KEY `fk_supplies_markets` (`market_id`),
 KEY `fk_supplies_currencies` (`currency_id`),
 CONSTRAINT `fk_supplies_currencies` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`currency_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_supplies_subjects` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_supplies_markets` FOREIGN KEY (`market_id`) REFERENCES `markets` (`market_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Another possible improvement can be removing cascade constraints. Check if they are really necessary.

Upvotes: 1

Related Questions