1

I have one small table (200 rows) and two large tables (200k rows each).

Small table has primary key id and some other fields.

First big table has id field (not a primary key), which are duplicated, but sorted numbers and a matchresult_id field, which are id's from a second big table.

Second big table has a field matchresult_id (primary key) and some other fields.

The query:

SELECT big2.*
FROM small_table s1
       INNER JOIN small_table s2 ON s1.id = s2.parent_id
       INNER JOIN big_table1 big1 ON s2.id = big1.id
       INNER JOIN big_table2 big2 ON big1.matchresult_id = big2.matchresult_id
ORDER BY big2.matchresult_id

The query takes over 1 second to complete. Without ORDER BY it takes 0.003 seconds. How to optimize the query?

Edit: Tables structure (some columns are omitted and names changed for the greater simplicity):

CREATE TABLE `small_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `catalogue_group_parent_id` int(11) NOT NULL,
 #.. plus many more columns
 PRIMARY KEY (`catalogue_group_id`),
 KEY `idx_catalogue_group_parent_id` (`catalogue_group_parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8

CREATE TABLE `big_table1` (
 `id` int(11) NOT NULL,
 `matchresult_id` int(11) NOT NULL,
 `catalogue_group2item_primary` tinyint(1) NOT NULL DEFAULT '0',
 KEY `idx_catalogue_group2item_groupid` (`id`),
 KEY `idx_catalogue_group2item` (`id`,`matchresult_id`),
 KEY `idx_catalogue_group2item_matchresultid` (`matchresult_id`),
 KEY `catalogue_id` (`id`,`matchresult_id`,`catalogue_group2item_primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `big_table2` (
 `matchresult_id` int(11) NOT NULL AUTO_INCREMENT,
 `matchresult_datecreated` datetime NOT NULL,
 `matchresult_datemodified` datetime NOT NULL,
  #.. plus many more columns
 PRIMARY KEY (`matchresult_id`),
 KEY `match_id` (`match_id`)
) ENGINE=InnoDB AUTO_INCREMENT=214477 DEFAULT CHARSET=utf8
  • Welcome to the site? Did you try with Left Outer Join in place of Inner JOIN ** . Because Left Outer Join **return all records from the left table, and the matched records from the right table. And also try to use the Group BY clause. – Md Haidar Ali Khan Apr 22 at 10:25
  • 1
    LEFT OUTER JOIN has nothing to do with the query (speed) I'm trying to solve, also GROUP BY? why? I need all results from big2 table. – osemec Apr 22 at 10:36
  • @MdHaidarAliKhan Changing the join type will alter the query logic. I doubt it is correct. – Akina Apr 22 at 10:37
  • @osemec The query takes over 1 second to complete. Without ORDER BY it takes 0.003 seconds. This looks like the optimizer have no enough time for to build correct execution plan, or there is too low memory for sorting. Try to rewrite the FROM section backward (all joins are inner, so the tables order doesn't matter) or to carteszian. I think small_table (parent_id, id) will slightly help. And look at the size of sorting buffer - maybe it is not large enough. And add query's EXPLAIN. – Akina Apr 22 at 10:39
  • @Akina Writing the query backwards doesn't change the speed. – osemec Apr 22 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.