Re: Query optimize on large database

[Pavel Volkov]

Here's table structure:

CREATE TABLE `data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` int(11) NOT NULL DEFAULT '0',
`artist` int(11) NOT NULL DEFAULT '0',
`title` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),
KEY `FK_data_Title_id` (`title`),
KEY `owner_id` (`owner_id`),
KEY `song` (`artist`,`title`),
KEY `FK_data_Artist_id` (`artist`),

CONSTRAINT `FK_data_Artists_id` FOREIGN KEY (`artist`) REFERENCES `Artists` (`id`),
CONSTRAINT `FK_data_Title_id` FOREIGN KEY (`title`) REFERENCES `Title` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=328181938 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=37


QUERY EXPLAIN :


SIMPLE d1 ref FK_data_Title_id,owner_id, song,FK_data_Artist_id owner_id 4 const 328 Using temporary; Using filesort

SIMPLE d2 ref FK_data_Title_id,song, FK_data_Artist_id song 8 joystor1_1.d1.artist,joystor1_1.d1.title 4