REGEXP Query for SEF

Latest HikaShop seems to have added some queries for SEF. Our test site slowed down even more with the latest version. MySQL logs man queries which only return 1 or 2 results and the queries are taking 1 minute or more to complete.

This one took 1 minute and 27 seconds to complete:
SELECT product_id FROM prfx_hikashop_product
WHERE product_alias REGEXP '^ *chic.+pencil.+skirt.+ash.+gray.+womens.+petite *$'
OR
product_name REGEXP '^ *chic.+pencil.+skirt.+ash.+gray.+womens.+petite *$'

Mysql shows the query examined 2598890 rows to return 1 result. Is this how this portion of hikashop supposed to work? Is there something in the config we can change to speed this up or skip this query? It wasn't in the previous versions.

Thanks.

Hi there,

You can try to disable the option "Remove products and categories id in URLs" in System>Configuration>Features>SEF.
IT will basically remove this query and you should gain a lot of time

Another option would be to add an index on the product_alias and product_name table, it should also improve the speed.

Ouch! Removing those id numbers from the URLs is excactly what we wanted. When we do a CSV import and new products and categories have been inserted, those id numbers don't match previous numbers and google gets 404s when indexing the site.

REGEXP seems to be much slower than LIKE in SQL. We ran the slow queries at the mysql prompt. Then converted the queries to use LIKE instead of REGEXP and they run much faster. Not knowing exactly what the REGEXP is trying to do, but here's what we get:

Using REGEXP:
SELECT product_id FROM prefx_hikashop_product WHERE product_alias REGEXP '^ *the.+love.+that.+was.+lost.+womens.+long.+sleeve.+hooded.+t.+shirt *$' OR product_name REGEXP '^ *the.+love.+that.+was.+lost.+womens.+long.+sleeve.+hooded.+t.+shirt *$'

+

---

+
| product_id |
+

---

+
| 126875 |
+

---

+

48.853 seconds

Using LIKE:
SELECT product_id FROM prefx_hikashop_product WHERE product_alias LIKE 'the%love%that%was%lost%womens%long%sleeve%hooded%t%shirt%' OR product_name LIKE 'the%love%that%was%lost%womens%long%sleeve%hooded%t%shirt%'

+

---

+
| product_id |
+

---

+
| 126875 |
+

---

+

2.783 seconds

These two queries bring back the same product_id value. When would LIKE not work and bring back the wrong value(s) for product_id?

We understand regular expressions. REGEXP & LIKE are different. We are trying to point out that you are using REGEXP and the power of REGEXP may not be necessary in this situation.

We even added the % at the beginning of the string thinking we might be missing something that your ^ * picks up with REGEXP, but the results are identical so far. We've been testing for 48 hours with 12500 urls hit so far with 100% identical results. We were hoping you could quickly point out situations when our LIKE version wouldn't work.

There are some really neat builtin functions like REGEXP and RAND(), but REGEXP and order by RAND() get really slow once the number of rows scanned grows.

Thanks for the reply. We found this very behaviour last night and posted in the bugs section:
hikashop.com/forum/3-bug-report/866686-r...-in-urls-broken.html

This fails with REGEXP. I think you will have to come up with an entirely different strategy to remove the IDs from the URLs.

As far as LIKE vs REGEXP, our testing shows that LIKE is much faster although both bring back the identical INCORRECT results. One interesting note is that Google Merchant rejected our site b/c the pages were loading too slow. We switched to our LIKE implementation and Google Merchant reapproved the site.

We are eagerly awaiting the next release in hopes the ID removal from URLs works correctly and is not a big slow down to the sites.

Thx.