Optimierung von MySQL-Abfragen auf JOINed-Tabellen mit GROUP BY und ORDER BY ohne Verwendung von verschachtelten Abfragen

Question

Dies fühlt sich wie ein bisschen ein Anfänger SQL Frage zu mir, aber hier geht. Dies ist, was ich zu tun versuche:

• drei Tabellen miteinander verbinden: Produkte, Tags und eine Verknüpfungstabelle.
• die Tags in einem einzigen durch Komma getrennten Feld zusammenfassen (daher GROUP_CONCAT und GROUP BY)
• Begrenzung der Ergebnisse (auf 30)
• die Ergebnisse in der Reihenfolge des Erstellungsdatums anzeigen
• die Verwendung von Unterabfragen nach Möglichkeit vermeiden, da sie in einem Active Record Framework besonders unangenehm zu kodieren sind

Ich habe die beteiligten Tabellen am Ende dieses Beitrags beschrieben, aber hier ist die Abfrage, die ich durchführe

   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;

Es gibt etwa 280.000 Produkte, 130 Tags, 524.000 Verknüpfungsdatensätze, und ich habe die Tabellen ANALYSEIERT. Das Problem ist, dass die Ausführung über 80 Sekunden dauert (auf anständiger Hardware), was sich für mich falsch anfühlt.

Hier sind die EXPLAIN-Ergebnisse:

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    

Ich denke, es tut Dinge in der falschen Reihenfolge, d. h. ORDERing die Ergebnisse nach der Verknüpfung, mit einer großen temporären Tabelle, und dann LIMITing. Der Abfrageplan in meinem Kopf würde etwa so aussehen:

• ORDNEN Sie die Tabelle "Produkte" anhand des Schlüssels "erstellt".
• Gehen Sie jede Zeile durch und verknüpfen Sie sie mit den anderen Tabellen, bis das LIMIT von 30 erreicht ist.

Das hört sich einfach an, aber es scheint nicht so zu funktionieren - habe ich etwas übersehen?

---

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

---

Auf Ersuchen von Salman A. mit Informationen zum Profiling aktualisiert:

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

Die Tabellen sind:

Produkte = 84.1MiB (es gibt zusätzliche Felder in der Produkttabelle, die ich aus Gründen der Übersichtlichkeit weggelassen habe) Schlagwörter = 32KiB Verknüpfungstabelle = 46.6MiB

Answer 1

Ich würde versuchen, die Anzahl der Produkte auf 30 zu begrenzen. erste und dann mit nur 30 Produkten einsteigen:

   SELECT p.*, GROUP_CONCAT(pt.name) as tags
     FROM (SELECT p30.* FROM products p30 ORDER BY p30.created LIMIT 30) 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  

Ich weiß, Sie sagten keine Unterabfragen aber Sie haben nicht erklärt, warum, und ich sehe keine andere Möglichkeit, Ihr Problem zu lösen.

Beachten Sie, dass Sie die Unterauswahl eliminieren können, indem Sie sie in eine Ansicht einfügen:

CREATE VIEW v_last30products AS 
  SELECT p30.* FROM products p30 ORDER BY p30.created LIMIT 30; 

Dann wird die Abfrage vereinfacht zu:

   SELECT p.*, GROUP_CONCAT(pt.name) as tags
     FROM v_last30products 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  

Anderes Thema, Ihr n-to-n Tabelle product_tags_for_products

Das macht keinen Sinn, ich würde es so umstrukturieren:

CREATE TABLE `product_tags_for_products` (    
  `product_id` mediumint(8) unsigned NOT NULL,    
  `product_tag_id` int(10) unsigned NOT NULL,    
  PRIMARY KEY (`product_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  

Dies sollte die Abfrage um einiges schneller machen:
- Verkürzung des verwendeten Schlüssels (bei InnoDB ist der PK immer in den Sekundärschlüsseln enthalten);
- Damit können Sie die PK verwenden, was schneller sein sollte als die Verwendung eines Sekundärschlüssels;

Mehr Geschwindigkeitsprobleme
Wenn Sie die select * mit nur den Feldern, die Sie benötigen select p.title, p.rating, ... FROM Das wird die Sache auch ein wenig beschleunigen.

Answer 2

Ah - ich sehe, dass keiner der Schlüssel, die Sie GROUP BY auf BTREE sind, standardmäßig PRIMARY-Schlüssel sind Hashes. Es hilft gruppieren nach, wenn es eine Bestellung Index... sonst hat es zu scannen...

Was ich meine, ist, ich denke, es würde erheblich helfen, wenn Sie einen BTREE-basierten Index für p.id und p.created hinzufügen. In diesem Fall denke ich, dass die Engine es vermeiden wird, all diese Schlüssel zu scannen/sortieren, um Gruppieren nach und Ordnen nach auszuführen.

Answer 3

Bezüglich der Filterung nach Tags (die Sie in den Kommentaren zu Johan's Antwort ), wenn die offensichtliche

SELECT p.*, GROUP_CONCAT(pt.name) AS tags
FROM products p
  JOIN product_tags_for_products pt4p2 ON (pt4p2.product_id = p.id)
  JOIN product_tags pt2 ON (pt2.id = pt4p2.product_tag_id)
  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)
WHERE pt2.name IN ('some', 'tags', 'here')
GROUP BY p.id
ORDER BY p.created LIMIT 30

nicht schnell genug läuft, können Sie dies versuchen:

CREATE TEMPORARY TABLE products30
  SELECT p.*
  FROM products p
    JOIN product_tags_for_products pt4p ON (pt4p.product_id = p.id)
    JOIN product_tags pt ON (pt.id = pt4p.product_tag_id)
  WHERE pt.name IN ('some', 'tags', 'here')
  GROUP BY p.id
  ORDER BY p.created LIMIT 30

SELECT p.*, GROUP_CONCAT(pt.name) AS tags
FROM products30 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

(Ich habe eine temporäre Tabelle verwendet, weil Sie sagten "keine Unterabfragen"; ich weiß nicht, ob sie in einem Active Record-Framework einfacher zu verwenden sind, aber es ist zumindest eine andere Möglichkeit, es zu tun).

---

Ps. Eine wirklich verrückte Idee zu Ihrem ursprünglichen Problem: Würde es einen Unterschied machen, wenn Sie die GROUP BY p.id Klausel zu GROUP BY p.created, p.id ? Wahrscheinlich nicht, aber ich würde es zumindest versuchen.