Neuigkeiten
  • Die modified eCommerce Shopsoftware ist kostenlos, aber nicht umsonst.
    Spenden
  • Damit wir die modified eCommerce Shopsoftware auch zukünftig kostenlos anbieten können:
    Spenden
  • Thema: SQL Fehler bei Produktfilter modified 2.0

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    SQL Fehler bei Produktfilter modified 2.0
    am: 03. Juni 2016, 16:45:22
    Hi, ich habe den Produktfilter aus modified 2.0 in Betrieb genommen. Wenn man mehrere Dinge gleichzeitig auswählt, kommt folgender SQL-Fehler per Email:

    Code: SQL  [Auswählen]
    1104 - The SELECT would examine more than MAX_JOIN_SIZE ROWS; CHECK your WHERE AND USE SET SQL_BIG_SELECTS=1 OR SET MAX_JOIN_SIZE=# IF the SELECT IS okay
     
     SELECT DISTINCT pto.options_id,
     pto.options_name,
     ptv.values_id,
     [...]
     AND pt5.options_id = '8'
     AND pt5.values_id = '72'
     WHERE p.products_status = '1'
     
     
     ORDER BY pto.sort_order, ptv.sort_order
     
     Request URL: meineshopurl.de/kategorie/Produkt:::15_1.html?cat=1&filter%5B1%5D=1&filter%5B5%5D=58&filter%5B11%5D=89&filter%5B6%5D=62&filter%5B8%5D=72
     
     [XT SQL Error]

    Nun habe ich schon gesucht und kann ausschließen, dass die max-join-size zu klein wäre (>2.000.000) und die MySql Version ist aktuell. Es passiert nicht bei der Suche sondern ausschließlich bei der Nutzung des Filters.
    Ich vermute, sollte ich der einzige sein, der dieses Problem hat, dass es damit zu tun hat, dass ich auf eine Beta von modified 2.0 up-ge-dated habe und nun die irgendwelche Indizes fehlen. Die Dateien habe ich natürlich schon aktualisiert, aber vielleicht war die initiale Installation fehlerhaft...

    Leider bin ich schon laaange nicht mehr SQL-fit, als dass ich sagen könnte, wie ich das korrigiere, insofern es daran liegt. Jemand ne Idee?  :?

    Linkback: https://www.modified-shop.org/forum/index.php?topic=35136.0
    Managed Server

    Bonsai

    • Viel Schreiber
    • Beiträge: 4.147
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #1 am: 06. Juni 2016, 11:28:09
    Geht wenigstens ein EXPLAIN des Queries oder wirft der auch Fehler?
    https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #2 am: 06. Juni 2016, 12:29:42
    ...
    Code: SQL  [Auswählen]
     [...]
     AND pt5.options_id = '8'
     AND pt5.values_id = '72'
     WHERE p.products_status = '1'
     ...


    ohne in den Code geschaut zu haben. Das sieht falsch aus. Da stehen doch Bedinungen vor der Where-Clause

    Bonsai

    • Viel Schreiber
    • Beiträge: 4.147
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #3 am: 06. Juni 2016, 12:49:00
    Guter Hinweis! Aber wie sieht der komplette Query aus? Das AND könnte ja auch von einer ON Clause kommen.

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #4 am: 06. Juni 2016, 14:08:25
    @Bonsai EXPLAIN funktioniert ohne Fehler.
    @webald Wie Bonsai es sagt, hier gehören die Bedingungen zum JOIN.

    Ich habe viele Dinge im Filter ausgewählt, sodass die Fehlermeldung wieder kam, mir die Query per "echo" ausgeben lassen und in myPHPadmin getestet. Explain geht, die Abfrage selbst Verursacht besagte Fehlermeldung.

    Hier ist die Abfrage aus der /includes/modules/listing_filter.php Zeile 187:

    Code: SQL  [Auswählen]
    $filterlist_sql = "SELECT DISTINCT pto.options_id,
                                         pto.options_name,
                                         ptv.values_id,
                                         ptv.values_name
                                    FROM "
    .TABLE_PRODUCTS." p
                                    JOIN "
    .TABLE_PRODUCTS_DESCRIPTION." pd
                                         ON p.products_id = pd.products_id
                                            AND pd.language_id = '"
    .$_SESSION['languages_id']."'
                                            AND trim(pd.products_name) != ''
                                    JOIN "
    .TABLE_PRODUCTS_TAGS." pt
                                         ON pt.products_id = p.products_id
                                    JOIN "
    .TABLE_PRODUCTS_TAGS_OPTIONS." pto
                                         ON pt.options_id = pto.options_id
                                            AND pto.languages_id = '"
    .$_SESSION['languages_id']."'
                                            AND pto.filter = '1'
                                    JOIN "
    .TABLE_PRODUCTS_TAGS_VALUES." ptv
                                         ON pto.options_id = ptv.options_id
                                            AND pt.values_id = ptv.values_id
                                            AND ptv.languages_id = '"
    .$_SESSION['languages_id']."'
                                            AND ptv.filter = '1'
                                         "
    .$join."
                                         "
    .$filter_join."
                                   WHERE p.products_status = '1'
                                         "
    .$where."
                                         "
    .PRODUCTS_CONDITIONS_P."
                                ORDER BY pto.sort_order, ptv.sort_order"
    ;

    Hier ist die Abfrage, wie sie dort zusammengepuzzelt wird, also so, wie sie zur DB geht:

    Code: SQL  [Auswählen]
    SELECT DISTINCT pto.options_id, pto.options_name, ptv.values_id, ptv.values_name FROM products p JOIN products_description pd ON p.products_id = pd.products_id AND pd.language_id = '2' AND TRIM(pd.products_name) != '' JOIN products_tags pt ON pt.products_id = p.products_id JOIN products_tags_options pto ON pt.options_id = pto.options_id AND pto.languages_id = '2' AND pto.filter = '1' JOIN products_tags_values ptv ON pto.options_id = ptv.options_id AND pt.values_id = ptv.values_id AND ptv.languages_id = '2' AND ptv.filter = '1' JOIN products_tags pt1 ON pt1.products_id = p.products_id AND pt1.options_id = '2' AND pt1.values_id = '83' JOIN products_tags pt2 ON pt2.products_id = p.products_id AND pt2.options_id = '1' AND pt2.values_id = '2' JOIN products_tags pt3 ON pt3.products_id = p.products_id AND pt3.options_id = '8' AND pt3.values_id = '72' JOIN products_tags pt4 ON pt4.products_id = p.products_id AND pt4.options_id = '5' AND pt4.values_id = '58' WHERE p.products_status = '1' AND p.manufacturers_id = '10'  AND p.products_id IN ('2', '4', '5', '8', '12', '13', '14', '16', '17', '18', '19', '20', '21', '22', '30', '750', '967', '968', '873', '59', '330', '327', '328', '66', '1846', '1844', '1841', '79', '591', '88', '89', '91', '93', '94', '95', '97', '98', '99', '103', '104', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '1820', '1821', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '138', '793', '140', '325', '1845', '1842', '1843', '795', '836', '835', '830', '831', '833', '995', '837', '189', '193', '194', '195', '196', '197', '198', '199', '200', '201', '202', '203', '204', '205', '206', '207', '208', '386', '214', '215', '216', '217', '218', '219', '994', '1839', '1840', '238', '1831', '242', '243', '244', '245', '246', '247', '249', '250', '251', '252', '285', '904', '292', '293', '295', '296', '300', '301', '302', '303', '304', '308', '309', '310', '312', '872', '314', '871', '870', '317', '319', '320', '321', '322', '323', '331', '332', '333', '334', '404', '388', '389', '390', '391', '392', '401', '402', '405', '387', '901', '902', '412', '413', '414', '415', '416', '417', '418', '419', '420', '421', '422', '423', '424', '425', '426', '427', '428', '429', '430', '431', '432', '433', '434', '435', '436', '437', '438', '439', '440', '441', '442', '443', '444', '445', '446', '447', '448', '980', '978', '979', '972', '974', '975', '976', '977', '971', '1937', '1936', '1934', '1935', '481', '484', '486', '487', '903', '999', '1000', '509', '511', '512', '514', '516', '518', '520', '521', '523', '525', '527', '529', '1001', '533', '534', '535', '536', '537', '538', '541', '1847', '1848', '1849', '1850', '1851', '1852', '1853', '797', '557', '796', '794', '792', '791', '790', '802', '801', '799', '868', '577', '578', '579', '580', '583', '584', '585', '586', '587', '588', '590', '592', '593', '594', '595', '987', '988', '599', '600', '617', '622', '624', '631', '989', '990', '991', '992', '993', '643', '644', '645', '647', '1953', '653', '654', '655', '656', '1063', '1062', '1798', '986', '981', '982', '983', '985', '673', '675', '677', '679', '680', '681', '683', '721', '722', '723', '724', '725', '726', '727', '728', '729', '730', '731', '732', '733', '734', '735', '736', '737', '738', '739', '740', '741', '742', '743', '744', '745', '746', '747', '748', '751', '752', '753', '754', '755', '756', '761', '762', '763', '827', '764', '773', '774', '777', '778', '779', '789', '803', '804', '807', '808', '811', '812', '813', '814', '815', '816', '817', '818', '819', '820', '821', '822', '823', '824', '825', '826', '828', '829', '834', '832', '838', '839', '840', '841', '842', '843', '844', '845', '846', '847', '848', '852', '853', '854', '855', '858', '859', '860', '861', '862', '863', '864', '865', '963', '876', '964', '965', '996', '998', '891', '924', '933', '934', '935', '936', '937', '943', '944', '945', '946', '947', '948', '949', '950', '951', '997', '1003', '1002', '970', '1007', '1008', '1009', '1010', '1011', '1012', '1013', '1014', '1015', '1016', '1017', '1018', '1019', '1020', '1021', '1031', '1022', '1023', '1024', '1025', '1026', '1027', '1028', '1029', '1030', '1032', '1033', '1034', '1035', '1036', '1037', '1038', '1039', '1040', '1041', '1042', '1043', '1044', '1045', '1046', '1047', '1048', '1050', '1051', '1052', '1053', '1055', '1056', '1057', '1058', '1059', '1060', '1061', '1752', '1753', '1754', '1755', '1756', '1757', '1758', '1759', '1760', '1761', '1762', '1763', '1764', '1765', '1766', '1767', '1771', '1769', '1770', '1772', '1773', '1774', '1775', '1777', '1778', '1779', '1780', '1781', '1782', '1783', '1784', '1787', '1790', '1791', '1792', '1793', '1794', '1795', '1796', '1797', '1799', '1800', '1833', '1801', '1802', '1803', '1804', '1805', '1810', '1808', '1811', '1812', '1813', '1814', '1815', '1816', '1819', '1822', '1823', '1824', '1825', '1826', '1830', '1828', '1829', '1832', '1834', '1835', '1836', '1837', '1838', '1854', '1855', '1857', '1858', '1860', '1861', '1862', '1863', '1864', '1865', '1868', '1867', '1869', '1870', '1871', '1872', '1873', '1874', '1875', '1876', '1877', '1878', '1884', '1932', '1933', '1930', '1931', '1885', '1889', '1887', '1888', '1890', '1891', '1892', '1893', '1894', '1895', '1897', '1898', '1903', '1904', '1905', '1906', '1907', '1908', '1909', '1910', '1911', '1912', '1913', '1914', '1915', '1916', '1917', '1918', '1919', '1921', '1922', '1923', '1924', '1925', '1926', '1927', '1928', '1929', '1938', '1939', '1940', '1941', '1942', '1943', '1944', '1945', '1946', '1947', '1948', '1949', '1950', '1954', '1952', '1951', '1955') ORDER BY pto.sort_order, ptv.sort_order

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #5 am: 07. Juni 2016, 07:53:57
    @Bonsai EXPLAIN funktioniert ohne Fehler.

    Das ist schön, aber was kommt raus?

    Bonsai

    • Viel Schreiber
    • Beiträge: 4.147
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #6 am: 07. Juni 2016, 11:49:13
    Jo .... mit der Ausgabe des EXPLAIN kann man herausfinden was das Problem ist.

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #7 am: 07. Juni 2016, 12:24:31
    Ich glaube, dass das
    ...
    Nun habe ich schon gesucht und kann ausschließen, dass die max-join-size zu klein wäre (>2.000.000) und die MySql Version ist aktuell. ...

    ein Irrtum ist und die vom sql-Server ermittelte join-size um ein vielfaches größer ist.

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #8 am: 07. Juni 2016, 16:41:16
    Die Angabe mit der MAX_JOIN_SIZE kommt vom Support meines Hosters.

    So sieht das Ergebnis des EXPLAIN aus:

      
    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1
    SIMPLEptALLidx_products_idNULLNULLNULL
    10398
    Using where; Using temporary; Using filesort
    1
    SIMPLEptveq_refPRIMARY,idx_options_idPRIMARY8meineDB.pt.values_id,const
    1
    Using where
    1
    SIMPLEpeq_refPRIMARY,idx_products_statusPRIMARY4meineDB.pt.products_id
    1
    Using where
    1
    SIMPLEpdeq_refPRIMARYPRIMARY5meineDB.pt.products_id,const
    1
    Using where
    1
    SIMPLEptoeq_refPRIMARYPRIMARY8meineDB.ptv.options_id,const
    1
    Using where
    1
    SIMPLEpt1refidx_products_ididx_products_id4meineDB.pd.products_id
    16
    Using where; Distinct
    1
    SIMPLEpt2refidx_products_ididx_products_id4meineDB.p.products_id
    16
    Using where; Distinct
    1
    SIMPLEpt3refidx_products_ididx_products_id4meineDB.pt.products_id
    16
    Using where; Distinct
    1
    SIMPLEpt4refidx_products_ididx_products_id4meineDB.pt2.products_id
    16
    Using where; Distinct

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #9 am: 07. Juni 2016, 17:09:38
    10.398 x 16 x 16 x 16 x 16 = 681.443.328

    Wenn ich nicht ganz falsch liege ist das der Wert den der sql-Server als join-size für diese Abfrage annimmt. Womit wir geringfügig über 2.000.000 wären.

    Ist das denn realistisch, dass eine solche Abfrage kommt oder ist das jetzt nur der ultimative Stresstest? Falls ja muss man drüber nachdenken, wie man das umbauen kann.

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #10 am: 07. Juni 2016, 17:55:58
    Aaahha! Ich lerne dazu! Da liegt also das Kaninchen im Senf begraben. Danke webald!

    Leider ist das kein Stresstest, sondern eine ganz normale Anwendung. Mit dem Filter schränkt man halt Schritt für Schritt die Auswahl ein, bis man hat, was man will und unsere Konkurrenz hat teilweise sogar komplexere Filter.

    Hab mal versucht, es in SQL mit LIMIT zu machen, funktioniert aber nicht. Der Syntax schon, allerdings wirft der MySQL den Fehler vorab schon raus.

    Dann habe ich noch einmal mit meinem Hoster gesprochen, wie es wäre, wenn ich "SET SQL_BIG_SELECTS=1" for das Query setze. Der Support meinte, dass er mir keine Limits jenseits vom max_join_size nennen könne und ich es lediglich ausprobieren könne, um zu sehen, ob der mysql demon danach noch lebt.
    Wir haben einen "betreuten V-Server", wo ich also keinen direkten Zugriff auf die demons habe. Ich hab es trotzdem mal ausprobiert und es geht ohne Probleme. Allerdings gefällt mir der Gedanke nicht, es so für den Live-Betrieb zu übernehmen.

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #11 am: 07. Juni 2016, 18:10:04
    Wir können mal was versuchen:

    Code: SQL  [Auswählen]
    ALTER TABLE `products_tags` ADD INDEX(`options_id`);
    ALTER TABLE `products_tags` ADD INDEX(`values_id`);

    Dann mach noch mal eine Explain und poste das Ergebnis.

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #12 am: 08. Juni 2016, 15:28:31
    Genau danach habe ich gesucht und das scheint es auch gewesen zu sein.
    Die gleiche Abfrage, die zuvor zu einem Fehler geführt hat, gibt phpMyAdmin nun ohne jeglichen Fehler aus.

    Unter EXPLAIN kommt folgendes dabei heraus:

     
      
    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1
    SIMPLEpt4index_mergeidx_products_id,options_id,values_idvalues_id,options_id4,4NULL
    12
    Using intersect(values_id,options_id); Using where...
    1
    SIMPLEpt2refidx_products_id,options_id,values_ididx_products_id4meineDB.pt4.products_id
    16
    Using where
    1
    SIMPLEpt1refidx_products_id,options_id,values_ididx_products_id4meineDB.pt2.products_id
    16
    Using where
    1
    SIMPLEpt3refidx_products_id,options_id,values_ididx_products_id4meineDB.pt2.products_id
    16
    Using where
    1
    SIMPLEpeq_refPRIMARY,idx_products_statusPRIMARY4meineDB.pt4.products_id
    1
    Using where
    1
    SIMPLEpdeq_refPRIMARYPRIMARY5meineDB.pt3.products_id,const
    1
    Using where
    1
    SIMPLEptrefidx_products_id,options_id,values_ididx_products_id4meineDB.pt3.products_id
    16
    Using where
    1
    SIMPLEptoeq_refPRIMARYPRIMARY8meineDB.pt.options_id,const
    1
    Using where
    1
    SIMPLEptveq_refPRIMARY,idx_options_idPRIMARY8meineDB.pt.values_id,const
    1
    Using where

    Da macht die Multiplikation selbst mir weniger Kopfschmerzen.
    Ich denke, so geht es. Auch in der Frontend-Anwendung funktioniert es, soweit ich das überblicken kann. Kann ich das so in meinen Live-Shop übernehmen oder gibt es da noch etwas zu beachten?

    webald

    • modified Team
    • Beiträge: 2.795
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #13 am: 08. Juni 2016, 15:44:34
    Kannst Du übernehmen. Ich habe ein Ticket draus gemacht, da das für die Nutzbarkeit wesentlich ist.

    Parker

    • Mitglied
    • Beiträge: 138
    • Geschlecht:
    Re: SQL Fehler bei Produktfilter modified 2.0
    Antwort #14 am: 08. Juni 2016, 18:05:53
    DB gesichert, durchgeführt, getestet und gefreut!  :B

    Ich denke, dass dies vielleicht auch folgenden Thread inhaltlich betrifft:
    BAUSTELLE: Zusätzliche Indizes für die Datenbank 
    http://www.modified-shop.org/forum/index.php?topic=963.0
    Dafür reicht meine Expertise aber leider nicht und diesen Thread hier markiere ich als gelöst.

    Vielen lieben Dank @webald und @Bonsai!  :thumbs:
    32 Antworten
    11664 Aufrufe
    17. Juni 2020, 22:01:01 von sven8965
    2 Antworten
    2693 Aufrufe
    13. Mai 2013, 09:09:04 von keineLustmehr
    2 Antworten
    2446 Aufrufe
    06. Juli 2015, 09:14:20 von voodoopupp
    2 Antworten
    3083 Aufrufe
    11. Juli 2014, 10:31:26 von Bonsai