SELECT
b.isbn,
booktitle,
price,
description,
GROUP_CONCAT(c.contributorid SEPARATOR '|' ) AS contributorids,
GROUP_CONCAT(fname, ' ', lname SEPARATOR '|' ) AS contributornames
FROM (book AS b
LEFT JOIN book_contributor AS bc
ON b.isbn = bc.isbn)
LEFT JOIN contributor AS c
ON bc.contributorid = c.contributorid
WHERE categoryid = '12'
and c.contributortype = 'forfattare'
group by b.isbn
order by contributornames
LIMIT 10
I want to optimize this query. book table contains almost 2,00,000 records, author contains 1,25,000 records and book_author has 3,25,000 records. With such a huge data, the query runs slow. Bascically I need to get author names in a comma separated string. when I don't need the result sorted on author names, I run two queries, one that get data from book table only and one that get data from (book_author and author join based on book's isbn).
here is the create for the tables
CREATE TABLE `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`isbn` VARCHAR(13) CHARACTER SET latin1 NOT NULL DEFAULT '',
`isbn10` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL,
`booktitle` VARCHAR(255) COLLATE utf8_swedish_ci DEFAULT NULL,
`preliminary_title` VARCHAR(255) COLLATE utf8_swedish_ci DEFAULT NULL,
`originaltitle` VARCHAR(255) CHARACTER SET latin1 DEFAULT NULL,
`mediatype` ENUM('Bok','Ljudbok','Multimedia','Marknadsföringsmaterial','Övrigt') COLLATE utf8_swedish_ci DEFAULT NULL,
`binding` ENUM('Kartonnage','Halvfranskt','Häftad','Inbunden','Pocket','Spiral','Storpocket','Flexband','Danskt band','Board book','Övrigt') COLLATE utf8_swedish_ci DEFAULT NULL,
`publisherid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`original_publisherid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`head_distributorid` INT(10) NOT NULL DEFAULT '0',
`distributorid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`stock_status` ENUM('Ej utkommen','Finns i lager','Tillfälligt slut','Definitivt slut','Spärrad','Utkommer ej') COLLATE utf8_swedish_ci DEFAULT NULL,
`stock` INT(3) NOT NULL DEFAULT '0',
`expected_availablity_date` DATE NOT NULL DEFAULT '0000-00-00',
`cost` INT(6) UNSIGNED NOT NULL DEFAULT '0',
`vat` DOUBLE(5,2) UNSIGNED NOT NULL DEFAULT '0.00',
`rea_ar` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`rea_fpris` DOUBLE(10,2) NOT NULL DEFAULT '0.00',
`rea_capris` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`reapris_fran_datum` DATE NOT NULL DEFAULT '0000-00-00',
`price` INT(6) NOT NULL DEFAULT '0',
`releasedate` DATE NOT NULL DEFAULT '0000-00-00',
`edition` INT(2) NOT NULL DEFAULT '0',
`pages` INT(5) NOT NULL DEFAULT '0',
`width` INT(11) NOT NULL DEFAULT '0',
`height` INT(11) NOT NULL DEFAULT '0',
`backwidth` INT(11) NOT NULL DEFAULT '0',
`weight` INT(11) NOT NULL DEFAULT '0',
`lang` VARCHAR(32) COLLATE utf8_swedish_ci DEFAULT NULL,
`lang_code` VARCHAR(32) COLLATE utf8_swedish_ci DEFAULT NULL,
`categoryid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`subcategoryid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`description` TEXT COLLATE utf8_swedish_ci,
`imageavailable` ENUM('Y','N') COLLATE utf8_swedish_ci NOT NULL DEFAULT 'N',
PRIMARY KEY (`bookid`),
UNIQUE KEY `isbn` (`isbn`),
KEY `categoryid` (`categoryid`),
KEY `subcategoryid` (`subcategoryid`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MYISAM AUTO_INCREMENT=197614 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
CREATE TABLE `book_contributor` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`isbn` VARCHAR(13) NOT NULL DEFAULT '',
`contributorid` INT(10) NOT NULL DEFAULT '0',
`contributortype` ENUM('forfattare','redaktor','oversattare','illustrator','institution','upplasare','bibliografisk_person','fotograf','formgivare','artist','kompositor','pseudonym','ovrigt') CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `isbn` (`isbn`),
KEY `contributorid` (`contributorid`)
) ENGINE=MYISAM AUTO_INCREMENT=325086 DEFAULT CHARSET=latin1
CREATE TABLE `contributor` (
`contributorid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
`lname` VARCHAR(100) NOT NULL DEFAULT '',
`contributortype` ENUM('forfattare','redaktor','oversattare','illustrator','institution','upplasare','bibliografisk_person','fotograf','formgivare','artist','kompositor','pseudonym','ovrigt') CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL COMMENT '''forfattare'' (author), ''redaktor'' (editor), ''oversattare'' (translator), ''illustrator'' (illustrator), ''institution'' (institution), ''upplasare'' (reader of an audio book),'' bibliografisk_person'' (bibliographical person), ''fotograf'' (photograph),''formgivare'' ',
`url` TINYTEXT,
PRIMARY KEY (`contributorid`),
KEY `fname` (`fname`),
KEY `lname` (`lname`)
) ENGINE=MYISAM AUTO_INCREMENT=122884 DEFAULT CHARSET=latin1
and here is the explain
+----+-------------+-------+--------+--------------------+------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------+------------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | b | ref | isbn,categoryid | categoryid | 4 | const | 4091 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | bc | ref | isbn,contributorid | isbn | 15 | bokliv.b.isbn | 2 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | bokliv.bc.contributorid | 1 | Using where |
+----+-------------+-------+--------+--------------------+------------+---------+-------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)