StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimising MySQL query on JOINed tables with GROUP BY and ORDER BY without using nested queries
text
Body
copied!<p>This feels like a bit of a beginner SQL question to me, but here goes. This is what I'm trying to do:</p> <ul> <li>join three tables together, products, tags, and a linking table. </li> <li>aggregate the tags into a single comma delimited field (hence the GROUP_CONCAT and the GROUP BY)</li> <li>limit the results (to 30)</li> <li>have the results in order of the 'created' date </li> <li>avoid using subqueries where possible, as they're particularly unpleasant to code using an Active Record framework</li> </ul> <p>I've described the tables involved at the bottom of this post, but here's the query that I'm performing</p> <pre><code> SELECT p.*, GROUP_CONCAT(pt.name) FROM products p LEFT JOIN product_tags_for_products pt4p ON (pt4p.product_id = p.id) LEFT JOIN product_tags pt ON (pt.id = pt4p.product_tag_id) GROUP BY p.id ORDER BY p.created LIMIT 30; </code></pre> <p>There are about 280,000 products, 130 tags, 524,000 linking records and I've ANALYZEd the tables. The problem is that it's taking over 80s to run (on decent hardware), which feels wrong to me.</p> <p>Here's the EXPLAIN results:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p index NULL created 4 NULL 30 "Using temporary" 1 SIMPLE pt4p ref idx_product_tags_for_products idx_product_tags_for_products 3 s.id 1 "Using index" 1 SIMPLE pt eq_ref PRIMARY PRIMARY 4 pt4p.product_tag_id 1 </code></pre> <p>I think it's doing things in the wrong order, i.e. ORDERing the results after the join, using a large temporary table, and then LIMITing. The query plan in my head would go something like this:</p> <ul> <li>ORDER the products table using the 'created' key</li> <li>Step through each row, LEFT JOINing it against the other tables until the LIMIT of 30 has been reached.</li> </ul> <p>This sounds simple, but it doesn't seem to work like that - am I missing something?</p> <hr> <pre><code>CREATE TABLE `products` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `rating` float NOT NULL, `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `active` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `created` (`created`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `product_tags_for_products` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_id` mediumint(8) unsigned NOT NULL, `product_tag_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_product_tags_for_products` (`product_id`,`product_tag_id`), KEY `product_tag_id` (`product_tag_id`), CONSTRAINT `product_tags_for_products_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `product_tags_for_products_ibfk_2` FOREIGN KEY (`product_tag_id`) REFERENCES `product_tags` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `product_tags` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci </code></pre> <hr> <p>Updated with profiling information at Salman A's request:</p> <pre><code>Status, Duration,CPU_user,CPU_system,Context_voluntary,Context_involuntary,Block_ops_in,Block_ops_out,Messages_sent,Messages_received,Page_faults_major,Page_faults_minor,Swaps,Source_function,Source_file,Source_line starting, 0.000124,0.000106,0.000015,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL "Opening tables", 0.000022,0.000020,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_base.cc,4519 "System lock", 0.000007,0.000004,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",lock.cc,258 "Table lock", 0.000011,0.000009,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",lock.cc,269 init, 0.000055,0.000054,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,2524 optimizing, 0.000008,0.000006,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,833 statistics, 0.000116,0.000051,0.000066,0,0,0,0,0,0,0,1,0,"unknown function",sql_select.cc,1024 preparing, 0.000027,0.000023,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1046 "Creating tmp table", 0.000054,0.000053,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1546 "Sorting for group", 0.000018,0.000015,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1596 executing, 0.000004,0.000002,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1780 "Copying to tmp table", 0.061716,0.049455,0.013560,0,18,0,0,0,0,0,3680,0,"unknown function",sql_select.cc,1927 "converting HEAP to MyISAM", 0.046731,0.006371,0.017543,3,5,0,3,0,0,0,32,0,"unknown function",sql_select.cc,10980 "Copying to tmp table on disk", 10.700166,3.038211,1.191086,538,1230,1,31,0,0,0,65,0,"unknown function",sql_select.cc,11045 "Sorting result", 0.777887,0.155327,0.618896,2,137,0,1,0,0,0,634,0,"unknown function",sql_select.cc,2201 "Sending data", 0.000336,0.000159,0.000178,0,0,0,0,0,0,0,1,0,"unknown function",sql_select.cc,2334 end, 0.000005,0.000003,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,2570 "removing tmp table", 0.106382,0.000058,0.080105,4,9,0,11,0,0,0,0,0,"unknown function",sql_select.cc,10912 end, 0.000015,0.000007,0.000007,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,10937 "query end", 0.000004,0.000002,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,5083 "freeing items", 0.000012,0.000012,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,6107 "removing tmp table", 0.000010,0.000009,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,10912 "freeing items", 0.000084,0.000022,0.000057,0,1,0,0,1,0,0,0,0,"unknown function",sql_select.cc,10937 "logging slow query", 0.000004,0.000001,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1723 "logging slow query", 0.000049,0.000031,0.000018,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1733 "cleaning up", 0.000007,0.000005,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1691 </code></pre> <p>The tables are:</p> <p>Products = 84.1MiB (there are extra fields in the products table which I omitted for clarity) Tags = 32KiB Linking table = 46.6MiB</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload