Unforgiving Search - Needs to be Case INsensitive

superscotty19 · Post by **superscotty19** » 30 Jun 2019, 00:09

Hi,

When I use the Product search, the big bar doesn't even work half the time - I'll search for an item I know I have and it won't appear. So, I go to the topmost search and although it works, it is case sensitive. In the following screenshot, I know there is a product named "Lana'i" - topmost search has it, big bar doesn't. In fact, the big bar is so strict that I can't even search for Lana'i - I have to search for the exact name (which, in this case is Specialized Lana'i).

Hoping this can be fixed?
-S.

superscotty19 · Post by **superscotty19** » 30 Jun 2019, 00:11

no items found?

superscotty19 · Post by **superscotty19** » 30 Jun 2019, 00:12

Search for Kona - Lana'i is missing in list

superscotty19 · Post by **superscotty19** » 30 Jun 2019, 00:13

search lowercase - no results

Post by **aimeos** » 30 Jun 2019, 10:16

Yes, the filter bar is a case sensitive prefix search, so you will only find your items when typing "Kona Lona". Case sensitivity is due to the table/column collation and the prefix search uses the index - otherwise, if you have some ten thousend items it would be extremely slow!

Currently not sure what the global search is using, but we will have a look at that.

superscotty19 · Post by **superscotty19** » 02 Jul 2019, 04:00

Hmmm ...

I thought the case sensitivity vs performance dichotomy was solved years ago with more powerful computers. Is the search dependent on the database structure, or can you run your own (binary?) search? I'm asking as a computer scientist, but I'm worried on the part of my users who, when entering data, will surely grow frustrated.

-S.

Post by **aimeos** » 02 Jul 2019, 09:31

You can change the collation of the mshop_product.label column to utf8mb4_general_ci or utf8_general_ci to make search case insensitive but that only works with MySQL so it's not a general option.

Using a prefix search (finds "Kona Lona" but not "Lona") is due to performance reasons. Otherwise, the database won't use the index and filtering would get very slow with more than a few thousand records.

superscotty19 · Post by **superscotty19** » 04 Jul 2019, 08:57

Hi,

Forgive my over-simplification, but I don't get it. I've never seen an e-commerce system in the past decade that is case sensitive. Heck, Google can index the world and even offer suggestions as you type! Where is the performance hit? Does an AJAX mechanism need to be used to speed things up so as to allow insensitivity?

-S.

Post by **aimeos** » 04 Jul 2019, 13:21

Like said, case insensitive search isn't a performance issue but it's only supported by MySQL so it's not a general available option. You can change the product label to case insensitivity by altering the mshop_product table:

Code: Select all

ALTER TABLE mshop_product MODIFY label VARCHAR(255) COLLATE utf8mb4_general_ci NOT NULL

Searching something inside the label (label LIKE '%luna%') IS getting a performance issue if you have some thousand records (Google doesn't use a relational database and it's one of the reasons why Magento backend is getting damn slow). If you know you will never have more than say 10k products, you can change the label filter in the product list from

Code: Select all

label LIKE 'luna%'

which is using the index and is fast to

Code: Select all

label LIKE '%luna%'

which is using no index and is slow by copying the product list template of the admin interface to your own exension and adding in line https://github.com/aimeos/ai-admin-jqad ... d.php#L611:

Code: Select all

['op' => '~=']

Hope this helps to satisfy your needs

superscotty19 · Post by **superscotty19** » 17 Jul 2019, 22:48

Ah, I see!

Thank you very much for the thorough explanation and workaround.

-Scott.

Help for Aimeos

Unforgiving Search - Needs to be Case INsensitive

Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Re: Unforgiving Search - Needs to be Case INsensitive

Connect with your social network account