mysql - 查询中的索引匹配

Question

我有一个字符串，我想用表行值搜索此字符串中最长的匹配项。在SQL中，此查询工作正常:

SELECT model, LENGTH(model) AS size 
FROM models 
WHERE 'Acer Aspire 3 A315-42' LIKE CONCAT('%', model, '%') 
ORDER BY size DESC 
LIMIT 1;

但这很慢。 Elasticsearch在索引中找到查询匹配项，但是我需要在查询中找到索引匹配项。 Elasticsearch 有什么解决方案吗？

Answer 1

脚本字段可用于对文本长度进行排序。更好的选择是也索引文本的长度并在该字段上排序。

数据:

"hits" : [
      {
        "_index" : "index16",
        "_type" : "_doc",
        "_id" : "QNLN5HEB9UrRQjNWOLGw",
        "_score" : 1.0,
        "_source" : {
          "model" : "Acer Aspire 3 A315-42  aa"
        }
      },
      {
        "_index" : "index16",
        "_type" : "_doc",
        "_id" : "QdLN5HEB9UrRQjNWQLFu",
        "_score" : 1.0,
        "_source" : {
          "model" : "Acer Aspire 3 A315-42"
        }
      }
    ]

查询:

{
  "query": {
    "match": {
      "model": "Acer Aspire 3 A315-42"
    }
  },
  "sort": [
    {
      "_script":{
        "script": "doc['model.keyword'].value.length()",-->script to return length
        "type": "number",
        "order": "desc"
      }
    }
  ],
  "size": 1 --> top 1
}

结果:

"hits" : [
      {
        "_index" : "index16",
        "_type" : "_doc",
        "_id" : "QNLN5HEB9UrRQjNWOLGw",
        "_score" : null,
        "_source" : {
          "model" : "Acer Aspire 3 A315-42  aa"
        },
        "sort" : [
          25.0
        ]
      }
    ]