Jump to content

Recommended Posts

Zdravím, prosím o pomoc s nasledovnou chybou

PrestaShop 1.6.1.17

www.preinterier.sk

 
Nainštaloval som v administrácii modul ktorý vytvára xml súbor pre Google Merchant Center. Modul funguje bez problémov no ma jednu chybu. Neumožňuje hromadnú úpravu produktov ako je napríklad vypnúť/zapnúť zahrnutie produktu do xml, alebo hromadné nastavenie zaradenia do kategórie podľa GMC.
 
E-shop obsahuje 18000 produktov a jediné čo mi napadlo ako tieto hodnoty upraviť hromadne a rýchlo bolo že to upravím priamo v DB a to v tabuľke "ps_product_shop.sql" .
Tabuľku som  zálohoval, kópiu tabuľky som v PSpade zmenil tak že som urobil hromadnú zmenu v príslušnom stĺpci a zmenil som "1" na "0" tým som vypol všetky produkty z xml stým že ich budem po kontrole, upravení parametrov, vložení EAN a pod. v administrácii postupne zapínať.
 
Pôvodnú tabuľku v DB som zmazal a nahral tam tú moju upravenú. Zmena v administrácii fungovala no malo to jeden nečakaný následok a to taký že od tohto momentu načítavanie Kategórii na FRONT stránky trvá strašne dlho.
 
V snahe napraviť chybu som upravenú tabuľku v DB zmazal a nahral tam pôvodnú zo zálohy no pomalé načítanie kategórii sa nezmenilo.
Najvýraznejšie sa chyba prejavuje pri kategórii "Fototapety" ktorá obsahuje najviac produktov. Načítavanie trvá aj viac ako 2 minúty.
 
 
požiadal o pomoc hosting či nevidia niekde v logu dôvod prečo načítavanie kategórii ide tak pomaly a ich odpoveď je takáto:
 
"
mate tam dost velky problem s pracou s databazou, konkretne takyto select:
 
 
CREATE TEMPORARY TABLE ps_cat_restriction ENGINE=MEMORY
                                                                                                        SELECT DISTINCT cp.id_product, p.id_manufacturer, product_shop.condition, p.weight FROM ps_category c
                                                                                                        STRAIGHT_JOIN ps_category_product cp ON (c.id_category = cp.id_category AND
                                                                                                        c.nleft >= 111
                                                                                                        AND c.nright <= 254
                                                                                                        AND c.active = 1)
                                                                                                        STRAIGHT_JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product
                                                                                                        AND product_shop.id_shop = 1)
                                                                                                        STRAIGHT_JOIN ps_product p ON (p.id_product=cp.id_product)
                                                                                                        WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog”)     

 

tento konkretne prehladal 158 milionov zaznamov (asi karteziansky sucin) a je tam vela takych, vzdy s niekolko milionmi zaznamov. Takze select je potrebne opravit.
 

este takyto, viac ako 5 mil. zaznamov:

 

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
                        pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
                        product_shop.`date_add` > "2018-01-05" as new,
MAX(product_attribute_shop.id_product_attribute) id_product_attribute
FROM `ps_product` p
 INNER JOIN ps_product_shop product_shop
                ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN `ps_product_lang` `pl` ON 
                        p.`id_product` = pl.`id_product`
                        AND pl.`id_lang` = 4 AND pl.id_shop = 1 
LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product`
 LEFT JOIN ps_image_shop image_shop
                ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 4
LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product`
 LEFT JOIN ps_product_attribute_shop product_attribute_shop
                ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1)
 LEFT JOIN ps_stock_available stock
                        ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND (p.`id_product` IN (18848,18821,18837,18822,18838,18823,18839,18824)) AND (p.`id_product` IN (
                                SELECT cp.`id_product`
                                FROM `ps_category_group` cg
                                LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`)
                                WHERE cg.`id_group` = 1
                        ))
GROUP BY product_shop.id_product
ORDER BY p.`date_add` DESC

 

este jeden, 11 mil. zaznamov:

 
SELECT COUNT(DISTINCT p.`id_product`)
                        FROM `ps_product` p
                         INNER JOIN ps_product_shop product_shop
                ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
                        WHERE product_shop.`active` = 1
                        AND product_shop.`show_price` = 1
                         AND product_shop.`visibility` IN ("both", "catalog")
                        AND p.`id_product` IN(2075, 2062, 2071, 2152, 2155, 2213, 2120, 2077, 2258, 2259, 2260, 2261, 2262, 2265, 2263, 2264, 2266, 2267, 2268, 2269, 2270, 2271, 2272, 2273, 2277, 2276, 2275, 2274, 2278, 2279, 2280, 2281, 2282, 2283, 2284, 2285, 3793, 2061, 2230, 2216, 2238, 2239, 2025, 2027, 2040, 2026, 2028, 2031, 2035, 2033, 5838, 5839, 5840, 5841, 5842, 5843, 5844, 5845, 5846, 5847, 5848, 5849, 5850, 5851, 5852, 5853, 5854, 5855, 5856, 5857, 5858, 5859, 5860, 5861, 5862, 5863, 5864, 5865, 5866, 5867, 5868, 5869, 5870, 5871, 5872, 5873, 5874, 5875, 5876, 5877, 5878, 5879, 5880, 5881, 5882, 5883, 5884, 5885, 5886, 5888, 5889, 5891, 5892, 5893, 5894, 5895, 5896, 5897, 5898, 5899, 5900, 5901, 5902, 5903, 5904, 5905, 5906, 5907, 5908, 5909, 5910, 5911, 5912, 5913, 5914, 5915, 5916, 5917, 5918, 5919, 5920, 5921, 5922, 5923, 5924, 2029, 2030, 2032, 2034, 2036, 2037, 2038, 2039, 2041, 2042, 2043, 2044, 2045, 2046, 2047, 2048, 2049, 2050, 2051, 2052, 2053, 2054, 2055, 2056, 2057, 2058, 2059, 2060, 2063, 2064, 2065, 2066, 2067, 2068, 2069, 2070, 2072, 2073, 2074, 2078, 2079, 2080, 2081, 2082, 2083, 2084, 2085, 2086, 2087, 2088, 2089, 2090, 2091, 2092, 2093, 2094, 2095, 2096, 2097, 2098, 2099, 2100, 2101, 2102, 2103, 2104, 2105, 2106, 2107, 2108, 2109, 2110, 2111, 2112, 2113, 2114, 2115, 2116, 2117, 2118, 2119, 2121, 2122, 2123, 2124, 2125, 2126, 2127, 2128, 2129, 2130, 2131, 2132, 2133, 2134, 2135, 2136, 2137, 2138, 2139, 2140, 2141, 2142, 2143, 2144, 2145, 2146, 2147, 2148, 2149, 2150, 2151, 2153, 2154, 2156, 2157, 2158, 2159, 2160, 2161, 2162, 2163, 2164, 2165, 2166, 2167, 2168, 2169, 2199, 2200, 2201, 2202, 2203, 2204, 2205, 2206, 2207, 2208, 2209, 2210, 2211, 2212, 2214, 2215, 2217, 2218, 2219, 2220, 2221, 2222, 2223, 2224, 2225, 2226, 2227, 2228, 2229, 2231, 2232, 2233, 2234, 2235, 2236, 2237, 2240, 2241, 2242, 2243, 2244, 2245, 2246, 2247, 2248, 2249, 2250, 2251, 2252, 2253, 2254, 2255, 2256, 2257, 2286, 2287, 2288, 2289, 2290, 2291, 2292, 2293, 2294, 2295, 2296, 2297, 2298, 2299, 2300, 2301, 2302, 2303, 2304, 2305, 2306, 2076, 6535, 6540, 6547, 6554, 6555, 6556, 6557, 6560, 6561, 6566, 6567, 6568, 6569, 6571, 6572, 6576, 6577, 6583, 6585, 6591, 6592, 6593, 6594, 6595, 6596, 6597, 6598, 6599, 6600, 6601, 6602, 6603, 6604, 6605, 6606, 6607, 6608, 6609, 6610, 6611, 6612, 6613, 6614, 6615, 6616, 6617, 6618, 6619, 6620, 6621, 6622, 6623, 6624, 6625, 6626, 6627, 6628, 6629, 6630, 6631, 6632, 6633, 6634, 6635, 6785, 6786, 6787, 6788, 6789, 6790, 6791, 6792, 6794, 6795, 6796, 6797, 6798, 6799, 6800, 6801, 6802, 6803, 6804, 6805, 6806, 6807, 6808, 6809, 6810, 6811, 6812, 6813, 6814, 6815, 6816, 6817, 6818, 6819, 6820, 6821, 6822, 6823, 6824, 6825, 6826, 6827, 6828, 6829, 6830, 6831, 6832, 6833, 6834, 6835, 6836, 6837, 6838, 6839, 6840, 6841, 6842, 6843, 6844, 6845, 6846, 6847, 6848, 6849, 6850, 6851, 6852, 6853, 6854, 6855, 6900, 6912, 6913, 6914, 6915, 7043, 7044, 7045, 863, 5658, 6579, 6558, 6590, 3462, 3462, 6531, 6532, 6537, 6534, 6541, 6536, 6542, 6543, 6546, 6564, 6544, 6549, 6550, 6575, 6573, 6559, 6582, 6581, 6563, 6589, 6586, 6539, 6545, 6551, 6538, 7706, 7707, 7708, 7709, 7865, 7866, 7867, 7868, 7869, 7870, 7871, 7872, 7873, 7874, 7875, 7876, 7877, 7878, 7879, 7880, 7881, 7882, 7883, 7884, 7885, 7886, 7887, 7888, 7889, 7890, 7891, 7892, 7893, 7894, 7895, 7896, 7897, 7898, 7899, 7900, 7901, 7902, 7903, 7177, 7111, 7162, 7163, 7161, 8236, 8268, 8269, 8270, 8271, 8272, 8273, 8274, 8275, 8276, 8277, 8278, 8279, 8280, 8281, 8282, 8283, 7710, 18151, 18152, 18153, 18154, 18155, 18156, 18157, 18158, 18159, 18161, 18162, 18163, 18164, 18165, 18166, 18167, 18168, 18169, 18170, 18171, 18172, 18173, 18174, 18175, 18176, 18177, 18178, 18179, 18180, 18181, 18182, 18183, 18184, 18185, 18186, 18187, 18188, 18189, 18190, 18191, 18192, 18193, 18194, 18195, 18196, 18197, 18198, 18199, 18200, 18201, 18202, 18203, 18204, 18205, 18206, 18207, 18208, 18209, 18210, 18211, 18212, 18213, 18214, 18215, 18216, 18217, 18218, 18219, 18220, 18221, 18149, 18150, 6492)
                         AND EXISTS(SELECT 1 FROM `ps_category_product` cp
                                JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1)
                                WHERE cp.`id_product` = p.`id_product`) LIMIT 1

 

"

 
 
Nie som programátor a neviem čo tento výpis chýb znamená a už vôbec ako túto chybu odstrániť.
 
Prosím ak máte niekto nápad v čom môže byť problém a ako to opraviť poraďte. Som ochotný za odstránenie chyby aj zaplatiť.
 
 
 
 
Edited by Jomko (see edit history)

Share this post


Link to post
Share on other sites

Opraveno. Zde je popis závady a její vyřešení:

 

V  tabulce ps_product_shop chyběly indexy. Při obnově tabulky bylo potřeba ještě vykonat dotaz pro vytvoření indexů. Ty se nacházely v jiném souboru a snadno se daly přehlédnout.

Celé to vzniklo tak, že v phpMyAdminu bylo při exportu zálohy nezvoleno "IF NOT EXISTS". Pak se indexy uloží do zvlášť, odděleně od tabulky. Je to ten méně častý případ, není to chyba, jen se s tím musí počítat a ty indexy vyhledat a aplikovat. U obnovy celé databáze je to jedno, protože tam se provádí všechny dotazy najednou a nic se nepřehlédne.

Pokud bude "IF NOT EXISTS" povoleno, pak se indexy exportují jako součást dotazu CREATE TABLE a vytvoří se automaticky při obnově databáze.

image.png.ad83c6ff44f84c934d3248596700f3db.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More