DLACZEGO WYSZUKIWARKI SĄ DO D*#%

Marcin
Lewandowski

PROBLEM 1

LIKE

PROBLEM 1

LIKE

PROBLEM 1

LIKE

LIKE %fraza%

LIKE %buty%

buty

PROBLEM 1

LIKE

LIKE %buty skórzane%

buty skórzane

PROBLEM 1

LIKE

LIKE %buty skórzane%

LIKE %buty% OR

LIKE %skórzane%

PROBLEM 1

LIKE

LIKE %buty skórzane%

LIKE %buty% AND

LIKE %skórzane%

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 3

LITERÓWKI

PROBLEM 3

LITERÓWKI

PROBLEM 3

LITERÓWKI

odległość = 1

PROBLEM 3

LITERÓWKI

-- Levenshtein function
-- Source: https://openquery.com.au/blog/levenshtein-mysql-stored-function
-- Levenshtein reference: http://en.wikipedia.org/wiki/Levenshtein_distance
-- Arjen note: because the levenshtein value is encoded in a byte array, distance cannot exceed 255;
-- thus the maximum string length this implementation can handle is also limited to 255 characters.
DELIMITER $$
DROP FUNCTION IF EXISTS LEVENSHTEIN $$
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR CHARACTER SET utf8;
-- max strlen=255 for this function
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1),
s2_len = CHAR_LENGTH(s2),
cv1 = 0x00,
j = 1,
i = 1,
c = 0;
IF (s1 = s2) THEN
RETURN (0);
ELSEIF (s1_len = 0) THEN
RETURN (s2_len);
ELSEIF (s2_len = 0) THEN
RETURN (s1_len);
END IF;
WHILE (j <= s2_len) DO
SET cv1 = CONCAT(cv1, CHAR(j)),
j = j + 1;
END WHILE;
WHILE (i <= s1_len) DO
SET s1_char = SUBSTRING(s1, i, 1),
c = i,
cv0 = CHAR(i),
j = 1;
WHILE (j <= s2_len) DO
SET c = c + 1,
cost = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);
SET c_temp = ORD(SUBSTRING(cv1, j, 1)) + cost;
IF (c > c_temp) THEN
SET c = c_temp;
END IF;
SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
IF (c > c_temp) THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, CHAR(c)),
j = j + 1;
END WHILE;
SET cv1 = cv0,
i = i + 1;
END WHILE;
RETURN (c);
END $$
DELIMITER ;
SELECT *  FROM `catalog_product_flat_1` WHERE
`name` LIKE '%_agento%' OR
`name` LIKE '%M_gento%' OR
`name` LIKE '%Ma_ento%' OR
`name` LIKE '%Mag_nto%' OR
`name` LIKE '%Mage_to%' OR
`name` LIKE '%Magen_o%' OR
`name` LIKE '%Magent_%' OR
`name` LIKE '%_Magento%' OR
`name` LIKE '%M_agento%' OR
`name` LIKE '%Ma_gento%' OR
`name` LIKE '%Mag_ento%' OR
`name` LIKE '%Mage_nto%' OR
`name` LIKE '%Magen_to%' OR
`name` LIKE '%Magent_o%' OR
`name` LIKE '%Magento_%' OR
`name` LIKE '%agento%' OR
`name` LIKE '%Mgento%' OR
`name` LIKE '%Maento%' OR
`name` LIKE '%Magnto%' OR
`name` LIKE '%Mageto%' OR
`name` LIKE '%Mageno%' OR
`name` LIKE '%Magent%'

Funkcja

LIKE

PROBLEM 4

JEZYKI

PROBLEM 4

JEZYKI

SKÓRA

SKÓRZANE

SKÓRY

PROBLEM 4

JEZYKI

PROBLEM 5

100 MS

PROBLEM 5

100 MS

PROBLEM 5

100 MS

PROBLEM 5

100 MS

PROBLEM 1

LIKE

PROBLEM 1

LIKE

buty skórzane

LIKE %buty% OR

LIKE %skórzane%

PROBLEM 1

LIKE

LIKE %buty%

OR

LIKE %skórzane%

"match": {
  "name": {
    "query": "buty skórzane",
    "operator": "or"
  }
}

PROBLEM 1

LIKE

LIKE %buty%

AND

LIKE %skórzane%

"match": {
  "name": {
    "query": "buty skórzane",
    "operator": "and"
  }
}

PROBLEM 1

LIKE

LIKE %buty%

OR

LIKE %skórzane%

"bool": {
  "must": [
    {
      "match": {
        "name": {
          "query": "buty skórzane",
          "operator": "or"
        }
      }
    }
  ],
  "should": [
    {
      "match": {
        "name": {
          "query": "buty skórzane",
          "operator": "and"
        }
      }
    }
  ]
}

LIKE %buty%

AND

LIKE %skórzane%

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 2

ZBYT SKOMPLIKOWANA

INDEX 1

INDEX 2

PROBLEM 2

ZBYT SKOMPLIKOWANA

PROBLEM 3

LITERÓWKI

"match": {
  "name": {
    "query": "buty skorzane",
    "fuzziness": 1
  }
}

PROBLEM 4

JEZYKI

GET _analyze
{
  "analyzer": "polish",
  "text": ["Anna miała czerwoną sukienkę."]
}

PROBLEM 5

100 MS

PROBLEM 5

100 MS

PROBLEM 5

100 MS

PROBLEM 5

100 MS

DZIĘKUJĘ ;)

Marcin
Lewandowski

PYTANIA ??

Marcin
Lewandowski

Dlaczego wyszukiwarki są do d*#%

By Marcin Lewandowski

Dlaczego wyszukiwarki są do d*#%

  • 516