[MySQL] Query langzaam, enige tips?

[NeFoRcE]

Heren,

Voor een webshop hebben we een query geschreven. Deze haalt uit een flinke genormeerde database gegevens op. Zo'n 20.000 producten staan erin, met bijbehorende gegevens.

Dit is de query:

SQL:

SELECT
    psv.value_id, psv.type_id, filter.filter_id,  filter.rename,
    filter.count_view, filter.count_fixed, filter.type_id,
    COUNT( psv.value_id ) as value_count,
    CONVERT(COALESCE ( psvi.value, psvf.value, psvv.value ) USING latin1) AS `value`
FROM p_filter_filter as filter                                                                         /* Heeft 4 records */
INNER JOIN p_specifications as ps ON filter.attribute_id = ps.attribute_id                             /* Heeft 21.000+ records */
INNER JOIN p_product p ON ps.product_id = p.product_id                                                 /* Heeft 5.000+ records */
INNER JOIN p_category_product as c ON ps.product_id = c.product_id                                     /* Heeft 6.000+ records */
INNER JOIN p_staffel as s ON ps.product_id = s.product_id                                              /* Heeft 5.000+ records */
INNER JOIN p_specifications_value as psv ON ps.spec_id = psv.spec_id                                   /* Heeft 25.000+ records */
LEFT JOIN p_specifications_int psvi ON psvi.type_id = psv.type_id AND psvi.value_id = psv.value_id     /* Heeft 6.000+ records */
LEFT JOIN p_specifications_float psvf ON psvf.type_id = psv.type_id AND psvf.value_id = psv.value_id   /* Heeft 1.000+ records */
LEFT JOIN p_specifications_varchar psvv ON psvv.type_id = psv.type_id AND psvv.value_id = psv.value_id /* Heeft 18.000+ records */
GROUP BY psv.value_id, psv.type_id
ORDER BY filter.index ASC , `value` ASC

Het runnen van de code:

code:

Showing rows 0 - 29 ( 125 total, Query took 0.3808 sec)

En hier de SQL dump voor de liefhebbers:

code:

-- phpMyAdmin SQL Dump
-- version 3.4.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 29, 2012 at 01:21 AM
-- Server version: 5.1.66
-- PHP Version: 5.3.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `*`
--

-- --------------------------------------------------------

--
-- Table structure for table `p_category_product`
--

CREATE TABLE IF NOT EXISTS `p_category_product` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `p_filter_filter`
--

CREATE TABLE IF NOT EXISTS `p_filter_filter` (
  `filter_id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) NOT NULL,
  `rename` varchar(50) NOT NULL,
  `type_id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `sql_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `child_visible` tinyint(1) NOT NULL,
  `sort_id` tinyint(1) NOT NULL,
  `sortable` tinyint(1) NOT NULL,
  `sortable_way` varchar(4) CHARACTER SET latin1 NOT NULL DEFAULT 'ASC',
  `count_view` tinyint(1) NOT NULL,
  `count_fixed` tinyint(1) NOT NULL,
  `hide` tinyint(1) NOT NULL,
  `index` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`filter_id`),
  KEY `attribute_id` (`attribute_id`),
  KEY `type_id` (`type_id`),
  KEY `sql_id` (`sql_id`),
  KEY `parent_id` (`parent_id`),
  KEY `sort_id` (`sort_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_product`
--

CREATE TABLE IF NOT EXISTS `p_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `visibility` tinyint(1) NOT NULL DEFAULT '0',
  `naam` varchar(120) NOT NULL,
  `onderschrift` varchar(120) NOT NULL,
  `c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `e_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_specifications`
--

CREATE TABLE IF NOT EXISTS `p_specifications` (
  `spec_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  PRIMARY KEY (`spec_id`),
  KEY `product_id` (`product_id`),
  KEY `attribute_id` (`attribute_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_specifications_float`
--

CREATE TABLE IF NOT EXISTS `p_specifications_float` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` tinyint(1) NOT NULL DEFAULT '3',
  `value` float NOT NULL,
  PRIMARY KEY (`value_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_specifications_int`
--

CREATE TABLE IF NOT EXISTS `p_specifications_int` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` tinyint(1) NOT NULL DEFAULT '2',
  `value` int(11) NOT NULL,
  PRIMARY KEY (`value_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_specifications_value`
--

CREATE TABLE IF NOT EXISTS `p_specifications_value` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `spec_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `spec_id` (`spec_id`),
  KEY `type_id` (`type_id`),
  KEY `value_id` (`value_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_specifications_varchar`
--

CREATE TABLE IF NOT EXISTS `p_specifications_varchar` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` tinyint(1) NOT NULL DEFAULT '1',
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`value_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4219 ;

-- --------------------------------------------------------

--
-- Table structure for table `p_staffel`
--

CREATE TABLE IF NOT EXISTS `p_staffel` (
  `staffel_id` int(11) NOT NULL AUTO_INCREMENT,
  `from` timestamp NULL DEFAULT NULL,
  `too` timestamp NULL DEFAULT NULL,
  `product_id` int(11) NOT NULL,
  `count` int(10) NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`staffel_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Constraints for dumped tables
--

--
-- Constraints for table `p_category`
--
ALTER TABLE `p_category`
  ADD CONSTRAINT `p_category_ibfk_3` FOREIGN KEY (`parent_id`) REFERENCES `p_category` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `p_category_product`
--
ALTER TABLE `p_category_product`
  ADD CONSTRAINT `p_category_product_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `p_category` (`category_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `p_category_product_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `p_product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `p_filter_filter`
--
ALTER TABLE `p_filter_filter`
  ADD CONSTRAINT `p_filter_filter_ibfk_4` FOREIGN KEY (`parent_id`) REFERENCES `p_filter_filter` (`filter_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `p_product`
--
ALTER TABLE `p_product`
  ADD CONSTRAINT `p_product_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user_login` (`user_id`) ON DELETE SET NULL ON UPDATE SET NULL;

--
-- Constraints for table `p_specifications`
--
ALTER TABLE `p_specifications`
  ADD CONSTRAINT `p_specifications_ibfk_1` FOREIGN KEY (`attribute_id`) REFERENCES `p_specifications_attributes` (`attribute_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `p_staffel`
--
ALTER TABLE `p_staffel`
  ADD CONSTRAINT `p_staffel_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `p_product` (`product_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

Is dit al de maximale optimalisatie? Of hebben we een belangrijk punt over het hoofd gezien???

[ Voor 5% gewijzigd door RobIII op 29-11-2012 10:09 . Reden: Query leesbaar gemaakt ]

[Kwastie]

Draai eens EXPLAIN <je query>. MySQL geeft je informatie over de uit te voeren query.

Ik verwacht dat je teveel JOINS gebruikt. Vervolgens doe je ook nog eens een GROUP BY

Kijk eens goed naar je Indexes, dat helpt ook altijd


Ignore wat er eerst stond, met indexes heb je hier niet zo veel last van

[ Voor 47% gewijzigd door Kwastie op 29-11-2012 01:10 ]

[NeFoRcE]

Hmm, na de explain krijg ik dit terug;

code:

+----+-------------+--------+--------+---------------------------------+--------------+---------+----------------------------------+------+---------------------------------+
| id | select_type | table  | type   | possible_keys                   | key          | key_len | ref                              | rows | Extra                           |
+----+-------------+--------+--------+---------------------------------+--------------+---------+----------------------------------+------+---------------------------------+
|  1 | SIMPLE      | filter | ALL    | attribute_id                    | NULL         | NULL    | NULL                             |    4 | Using temporary; Using filesort |
|  1 | SIMPLE      | ps     | ref    | PRIMARY,product_id,attribute_id | attribute_id | 4       | ws_standard2.filter.attribute_id |   54 |                                 |
|  1 | SIMPLE      | s      | ref    | product_id                      | product_id   | 4       | ws_standard2.ps.product_id       |    1 | Using index                     |
|  1 | SIMPLE      | c      | ref    | PRIMARY                         | PRIMARY      | 4       | ws_standard2.s.product_id        |    1 | Using where; Using index        |
|  1 | SIMPLE      | p      | eq_ref | PRIMARY                         | PRIMARY      | 4       | ws_standard2.c.product_id        |    1 | Using where; Using index        |
|  1 | SIMPLE      | psv    | ref    | spec_id                         | spec_id      | 4       | ws_standard2.ps.spec_id          |    1 |                                 |
|  1 | SIMPLE      | psvi   | eq_ref | PRIMARY,type_id                 | PRIMARY      | 4       | ws_standard2.psv.value_id        |    1 |                                 |
|  1 | SIMPLE      | psvf   | eq_ref | PRIMARY,type_id                 | PRIMARY      | 4       | ws_standard2.psv.value_id        |    1 |                                 |
|  1 | SIMPLE      | psvv   | eq_ref | PRIMARY,type_id                 | PRIMARY      | 4       | ws_standard2.psv.value_id        |    1 |                                 |
+----+-------------+--------+--------+---------------------------------+--------------+---------+----------------------------------+------+---------------------------------+

Maar die joins zijn wel nodig denk ik...

[pedorus]

Eerste indruk: Hoort bij iedere combinatie (psv.value_id, psv.type_id) echt maar 1 filter_id en 1 psvi.value/psvf.value/psvv.value, of is dit gewoon een onzinquery? (zoekterm: GROUP BY)

Tweede idee: wat probeer je eigenlijk te bereiken, en is de snelheid echt een probleem als je een cache gebruikt?

offtopic:
[code=sql] en minder witruimte zou dit veel leesbaarder maken.

[TheNameless]

Ik heb verder geen verstand van mysql maar het aantal joins lijkt mij hier het probleem.
Wat je daar tegen kan doen om voor je p_specification_values maar 1 tabel gebruiken met daarin 3 kolommen: value_int, value_float en value_varchar. Scheelt je een aantal joins

Maar goed dan moet je je hele database aanpassen, ik weet niet of dat haalbaar is voor jou.

[Tarilo]

Als je naar de explain kijkt zie je in de bovenste regel dat hij 'using temporay, using filesort' doet, dat is waarschijnlijk het grootste probleem. Die JOINS maken niet zo heel veel uit, aangezien die allemaal gebruik maken van indexes.

Ook wordt bij de bovenste regel een volledige tabelscan gedaan (type = ALL). Als je de performance van je query wilt verbeteren zul je dus daarop moeten focussen. edit: 4/4 rows betekent natuurlijk altijd een full tablescan

[ Voor 7% gewijzigd door Tarilo op 29-11-2012 09:04 ]

[D-Raven]

Wat Tarilo zegt. Probeer eerst eens om die table scan eruit te krijgen door een index in je Filter tabel te definieren.
Daarnaast, naar wat voor performance boost ben je op zoek? Wanneer is hij "snel genoeg"?

[Tarilo]

D-Raven schreef op donderdag 29 november 2012 @ 09:21:
Wat Tarilo zegt. Probeer eerst eens om die table scan eruit te krijgen door een index in je Filter tabel te definieren.
Daarnaast, naar wat voor performance boost ben je op zoek? Wanneer is hij "snel genoeg"?

Die tablescan is het probleem niet. Als het aantal records dat je ophaalt meer is dan een bepaald percentage van het totaal aantal records zal er altijd een tablescan uitgevoerd worden, omdat dit sneller is. In dit geval haalt hij 4/4 records op dus is de tablescan op die tabel niet verkeerd. (Mijn aanname was daar dus ook fout)

Een veel groter probleem is die filesort denk ik. Die gaat volgens mij over de resultaten van alle joins en filesort zijn nu eenmaal langzaam. Zoals hierboven aangegeven is de vraag of die GROUP BY wel klopt, maar dat zal de TS ons moeten vertellen.

edit: tablescan is niet verkeer.

[D-Raven]

Tarilo schreef op donderdag 29 november 2012 @ 09:40:
[...]
In dit geval haalt hij 4/4 records op dus is de tablescan op die tabel verkeerd. (Mijn aanname was daar dus ook fout)

Je spreekt jezelf tegen. Eerst zeg je dat de tablescan niet het probleem is, en vervolgens zeg je dat dat het wel 'verkeerd' is.

Ik heb ook niet gezegd dat de tablescan 'het' probleem is. Ik gaf alleen maar aan dat het zou kunnen helpen als daar een index op zit, nu zit er namelijk geen index op attribute_id. Terwijl daar zo te zien wel een scan op plaatsvind.

Vandaar ook mijn vraag naar hoe snel hij hem wilt hebben. Er zijn nogal wat voorstellen die je kunt doen hier, de een nog ingrijpender dan de ander. Dit hangt samen met hoe snel hij hem wilt hebben.

[Tarilo]

D-Raven schreef op donderdag 29 november 2012 @ 09:59:
[...]


Je spreekt jezelf tegen. Eerst zeg je dat de tablescan niet het probleem is, en vervolgens zeg je dat dat het wel 'verkeerd' is.

Ik heb ook niet gezegd dat de tablescan 'het' probleem is. Ik gaf alleen maar aan dat het zou kunnen helpen als daar een index op zit, nu zit er namelijk geen index op attribute_id. Terwijl daar zo te zien wel een scan op plaatsvind.

Vandaar ook mijn vraag naar hoe snel hij hem wilt hebben. Er zijn nogal wat voorstellen die je kunt doen hier, de een nog ingrijpender dan de ander. Dit hangt samen met hoe snel hij hem wilt hebben.

Daar moest inderdaad nog het woordje niet tussen. De tablescan is niet verkeerd in dit geval. Er zit ook een index op, kijk maar naar de explain. Bij possible keys staat de index op atribute_id. Deze wordt alleen niet gebruikt in dit geval.

Excuses voor de verwarring

[RobIII]

En zoals in zo'n beetje élk MySQL topic waar een GROUP BY in voorkomt (*sigh*): Hoe werkt dat GROUP BY nu eigenlijk?

[Cartman!]

Beetje doorgeslagen in denormalisatie denk ik, gewoon die specifaction_value_* tables eruit mikken en nullable velden erin mikken (of 1 enum met het type en 1 value varchar als je er niet op hoeft te sorteren).

[cariolive23]

RobIII schreef op donderdag 29 november 2012 @ 10:10:
En zoals in zo'n beetje élk MySQL topic waar een GROUP BY in voorkomt (*sigh*): Hoe werkt dat GROUP BY nu eigenlijk?

Kan Tweakers niet een automatische reply genereren wanneer deze combinatie voorkomt, met daarin de link naar genoemd artikel?

Met een simpele setting in MySQL (het liefst in my.cnf) kun je tenminste een foutmelding genereren wanneer je deze fout maakt:

SQL:

SET SQL_MODE = 'ONLY_FULL_GROUP_BY';

Het liefst ga je nog wat verder en alleen maar ANSI sql gebruiken en foutmeldingen genereren wanneer er wat fout gaat, maar dat is vaak teveel gevraagd bij bestaande software: Er komen dan pijnlijk veel bugs aan het licht...

[leuk_he]

waarom zie je bij

explain plan...

ws_standard2.ps.spec_id | 1 |

geen using index? terwijl daar veel rijen in zitten.

[Tarilo]

leuk_he schreef op vrijdag 30 november 2012 @ 15:33:
waarom zie je bij


[...]

geen using index? terwijl daar veel rijen in zitten.

Er wordt wel een index gebruikt, zoals ook is aangegeven in de kolom 'key'. 'using index' betekent dat er een covering index gebruikt is.