Does anyone else have this issue with search result long delays?

[jeffmic]

thank you for the incite. I didn't see a speed increase on a search just now, but hopefully this helps the problem with multiple searches at the same time.

Another question: Any ideas why mysql.sock always starts up in a temp location?

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

[RodG]

Any ideas why mysql.sock always starts up in a temp location?

Because that's what you've configured it to use... (it is also the default location for many installations)

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

If you wish to use a different location you'll need to set something like:
socket = /var/run/mysqld/mysql.sock

Keep in mind that you'll also need to change your client settings so they use the same location.

Cheers
Rod

[jeffmic]

This mysql clogging is never ending. 2 or 3 people are searching at this time each time one search is started, and then another is added, BOTH searches suffer and become clogged. Thank you to everyone's suggestions, but none have helped solve this problem. WHY is it when multiple clients hit my search, does it clog up like this? You'll see one clog below over 200 seconds! UGH. It's so frustrating. If anyone has the ability to repair this issue, I will gladly hire them.

I only use my sidebox search to look up Product ID, Product Title and metadata keywords. EVERYTHING else can be removed from the advanced search file.

# Time: 130807 15:22:55
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 64.001574  Lock_time: 0.000144 Rows_sent: 10  Rows_examined: 440100
SET timestamp=1375903375;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%josh%'                                          OR p.products_model                                          LIKE 'josh' OR (mtpd.metatags_keywords                         LIKE '%josh%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%groban%'                                          OR p.products_model                                          LIKE 'groban' OR (mtpd.metatags_keywords                         LIKE '%groban%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 70, 10;
# Time: 130807 15:23:02
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 71.266656  Lock_time: 0.000172 Rows_sent: 4  Rows_examined: 440416
SET timestamp=1375903382;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%lose%'                                          OR p.products_model                                          LIKE 'lose' OR (mtpd.metatags_keywords                         LIKE '%lose%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%a%'                                          OR p.products_model                                          LIKE 'a' OR (mtpd.metatags_keywords                         LIKE '%a%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%man%'                                          OR p.products_model                                          LIKE 'man' OR (mtpd.metatags_keywords                         LIKE '%man%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# Time: 130807 15:23:25
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 93.794725  Lock_time: 0.000177 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903405;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-01967%'                                          OR p.products_model                                          LIKE 'k-01967' OR (mtpd.metatags_keywords                         LIKE '%k-01967%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:24:51
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 171.782421  Lock_time: 0.000128 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903491;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:25:24
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 199.395655  Lock_time: 0.000210 Rows_sent: 10  Rows_examined: 440100
SET timestamp=1375903524;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%josh%'                                          OR p.products_model                                          LIKE 'josh' OR (mtpd.metatags_keywords                         LIKE '%josh%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%groban%'                                          OR p.products_model                                          LIKE 'groban' OR (mtpd.metatags_keywords                         LIKE '%groban%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 70, 10;
# Time: 130807 15:25:37
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 209.173067  Lock_time: 0.000141 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903537;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:25:48
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 217.377615  Lock_time: 0.000153 Rows_sent: 10  Rows_examined: 440100
SET timestamp=1375903548;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%josh%'                                          OR p.products_model                                          LIKE 'josh' OR (mtpd.metatags_keywords                         LIKE '%josh%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%groban%'                                          OR p.products_model                                          LIKE 'groban' OR (mtpd.metatags_keywords                         LIKE '%groban%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 70, 10;
# Time: 130807 15:25:53
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 220.713859  Lock_time: 0.000255 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903553;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:26:03
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 226.117873  Lock_time: 0.000556 Rows_sent: 10  Rows_examined: 440110
SET timestamp=1375903563;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%josh%'                                          OR p.products_model                                          LIKE 'josh' OR (mtpd.metatags_keywords                         LIKE '%josh%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%groban%'                                          OR p.products_model                                          LIKE 'groban' OR (mtpd.metatags_keywords                         LIKE '%groban%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 80, 10;
# Time: 130807 15:26:19
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 234.677315  Lock_time: 0.000300 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903579;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%per%'                                          OR p.products_model                                          LIKE 'per' OR (mtpd.metatags_keywords                         LIKE '%per%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%te%'                                          OR p.products_model                                          LIKE 'te' OR (mtpd.metatags_keywords                         LIKE '%te%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:26:32
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 240.397156  Lock_time: 0.000464 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903592;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%per%'                                          OR p.products_model                                          LIKE 'per' OR (mtpd.metatags_keywords                         LIKE '%per%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%te%'                                          OR p.products_model                                          LIKE 'te' OR (mtpd.metatags_keywords                         LIKE '%te%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:26:36
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 241.581298  Lock_time: 0.000609 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903596;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%kp-09119%'                                          OR p.products_model                                          LIKE 'kp-09119' OR (mtpd.metatags_keywords                         LIKE '%kp-09119%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:26:47
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 244.225539  Lock_time: 0.000421 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903607;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%per%'                                          OR p.products_model                                          LIKE 'per' OR (mtpd.metatags_keywords                         LIKE '%per%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%te%'                                          OR p.products_model                                          LIKE 'te' OR (mtpd.metatags_keywords                         LIKE '%te%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:26:53
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 245.721936  Lock_time: 0.000529 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903613;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-01557%'                                          OR p.products_model                                          LIKE 'k-01557' OR (mtpd.metatags_keywords                         LIKE '%k-01557%'                         AND mtpd.metatags_keywords !='')) ));
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 245.647362  Lock_time: 0.000525 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903613;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));

[jeffmic]

Here is more that got cut off in my last post

# Time: 130807 15:27:03
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 247.009971  Lock_time: 0.000125 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903623;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%mp-4188%'                                          OR p.products_model                                          LIKE 'mp-4188' OR (mtpd.metatags_keywords                         LIKE '%mp-4188%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:27:04
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 11.710314  Lock_time: 0.000197 Rows_sent: 4662  Rows_examined: 18648
SET timestamp=1375903624;
select  p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
       from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id
       where p.products_status = 1
         and p.products_id = p2c.products_id
         and pd.products_id = p2c.products_id
         and pd.language_id = '1'
         and p2c.categories_id = '140';
# Time: 130807 15:27:16
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 12.726630  Lock_time: 0.000142 Rows_sent: 4662  Rows_examined: 27972
SET timestamp=1375903636;
select p.products_image, pd.products_name, p.products_quantity,  p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
       from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id
       where p.products_status = 1
         and p.products_id = p2c.products_id
         and pd.products_id = p2c.products_id
         and pd.language_id = '1'
         and p2c.categories_id = '140' order by pd.products_name;
# Time: 130807 15:27:30
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 13.008905  Lock_time: 0.000167 Rows_sent: 1  Rows_examined: 18648
SET timestamp=1375903650;
select count(p.products_id) as total  from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id        where p.products_status = 1          and p.products_id = p2c.products_id          and pd.products_id = p2c.products_id          and pd.language_id = '1'          and p2c.categories_id = '140';
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 253.737601  Lock_time: 0.000159 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903650;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:27:41
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 255.728460  Lock_time: 0.000198 Rows_sent: 1  Rows_examined: 440413
SET timestamp=1375903661;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-01967%'                                          OR p.products_model                                          LIKE 'k-01967' OR (mtpd.metatags_keywords                         LIKE '%k-01967%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# Time: 130807 15:27:42
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 12.544475  Lock_time: 0.000209 Rows_sent: 10  Rows_examined: 23320
SET timestamp=1375903662;
select p.products_image, pd.products_name, p.products_quantity,  p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status        from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id        where p.products_status = 1          and p.products_id = p2c.products_id          and pd.products_id = p2c.products_id          and pd.language_id = '1'          and p2c.categories_id = '140' order by pd.products_name  limit 10;
# Time: 130807 15:28:20
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 259.304035  Lock_time: 0.000316 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903700;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-13124%'                                          OR p.products_model                                          LIKE 'k-13124' OR (mtpd.metatags_keywords                         LIKE '%k-13124%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:28:31
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 260.304268  Lock_time: 0.000423 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903711;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%m-3611%'                                          OR p.products_model                                          LIKE 'm-3611' OR (mtpd.metatags_keywords                         LIKE '%m-3611%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:28:34
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 260.176698  Lock_time: 0.000517 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903714;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%one%'                                          OR p.products_model                                          LIKE 'one' OR (mtpd.metatags_keywords                         LIKE '%one%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%hundred%'                                          OR p.products_model                                          LIKE 'hundred' OR (mtpd.metatags_keywords                         LIKE '%hundred%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%easy%'                                          OR p.products_model                                          LIKE 'easy' OR (mtpd.metatags_keywords                         LIKE '%easy%'                         AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%ways%'                                          OR p.products_model                                          LIKE 'ways' OR (mtpd.metatags_keywords                         LIKE '%ways%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:28:56
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 257.948096  Lock_time: 0.000303 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903736;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%kp-02517%'                                          OR p.products_model                                          LIKE 'kp-02517' OR (mtpd.metatags_keywords                         LIKE '%kp-02517%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:28:58
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 257.653265  Lock_time: 0.000323 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903738;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%60520102%'                                          OR p.products_model                                          LIKE '60520102' OR (mtpd.metatags_keywords                         LIKE '%60520102%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:29:00
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 257.212275  Lock_time: 0.000499 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903740;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%pageant%'                                          OR p.products_model                                          LIKE 'pageant' OR (mtpd.metatags_keywords                         LIKE '%pageant%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130807 15:29:29
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 253.313546  Lock_time: 0.000373 Rows_sent: 1  Rows_examined: 440414
SET timestamp=1375903769;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%if i die young%'                                          OR p.products_model                                          LIKE 'if i die young' OR (mtpd.metatags_keywords                         LIKE '%if i die young%'                         AND mtpd.metatags_keywords !='')) ));

[wilt]

Hi Jeff,

Did you get a chance to run one of your slow queries thru phpmyadmin with profiling.

[solo_400]

"May 6, 2008 - Not, this is not regression. But further investigation showed this seems to be only Windows and Mac issue. " ????

http://bugs.mysql.com/bug.php?id=36448

http://serverfault.com/questions/644...y-slow-on-os-x

[jeffmic]

profiling seems to be fine on any of the searches I did:

Starting 24 µs
Waiting For Query Cache Lock 3 µs
Checking Query Cache For Query 12 µs
Checking Privileges On Cached 3 µs
Checking Permissions 10 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 3 µs
Sending Cached Result To Clien 11 µs
Logging Slow Query 2 µs
Cleaning Up 2 µs

[jeffmic]

Does anyone recommend that I change my storage engine to Myisam to InnoDB? There is a contribution on Zencart by Data-Diggers, but it doesn't appear in the tools menu on Zencart 1.5.1. It is approved and DOES appear on 1.3.9 though, but I'm scared to press the change button.

[jeffmic]

When working and digging into this with Dr. Byte today, we seemed to have found a solution.

1 - We changed 3 tables to InnoDB (and anything with FullText had to be changed to Normal)
2 - A few changes were made to the keys and indexes, but they actually did little in those tables.

I have 2 - 1.5.1 websites that contain the same data in the databases.
So, after changing over 1 of them with Dr Byte and leaving the other as is for now, here's the search results on TWO CONCURRENT SEARCHES using different ip address.

Site 1 unchanged: 12.9 seconds after clogging mysql and then a white screen for a bit
Parse Time: 12.983 - Number of Queries: 184 - Query Time: 12.871974807755

Site 2 with changes: 2.4 seconds, immediate screen drawing of data with no clogging of mysql

So happy today - still testing and making adjustments as needed.

The 3 tables that I needed changed to InnoDB were:

meta_tags_products_description (as I use keywords for my song artist in the side box advanced search)
products
products_description

I filtered out manufacturers and categories in my header.php.php file for advanced-search so those tables didn't need to be changed to InnoDB at this time.

I'll update as I find out more. WHEW!!

[solo_400]

Good to know Jeff , thx for sharing that .