CODEWITHSUNDEEP
mysqlsqldatabase
AHMED.D
AHMED.D

Reputation: 165

Enhancing table MYSQL Performance

I have a table with the following scheme : 

CREATE TABLE `type_interests` (
  `id` int(11) NOT NULL,
  `interest_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `type` varchar(64) NOT NULL,
  `status` varchar(64) NOT NULL,
  `created_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `notes` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `type_interests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `interest_id` (`interest_id`,`type_id`),
  ADD KEY `interest_id_2` (`interest_id`);


ALTER TABLE `type_interests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31415;

ALTER TABLE `type_interests`
  ADD CONSTRAINT `type_interests_ibfk_1` FOREIGN KEY (`interest_id`) REFERENCES `interests` (`id`);

And i have only about 30,000 records in the database but all queries which use this table is consuming alot of time, is it a design problem? note that type_id is linked to more than one table.

Upvotes: 0

Views: 36

Answers (1)

Mecanik
Mecanik

Reputation: 1049

You should index your table properly, more information here: http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

It depends on your server aswell, you can "tune" your MySQL with this script: https://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

Upvotes: 1

Related Questions