改进SQL命令以按字母顺序显示前面和后面的值

Question

使用下面的两个SQL命令，我按字母顺序查找显示的词条前面的15个词条和显示的词条后面的15个词典。

关键字列已编入索引。规定了执行时间。

有没有更快或更好的解决方案可以在按字母顺序排列的大列表中找到几个前面和后面的值？

SELECT * 
FROM  
(SELECT 
`keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM 
`ds_1_headword` 
WHERE 
`keyword` COLLATE `utf8_icelandic_ci` <= 'Patagónía' 
ORDER BY 
`keyword` COLLATE `utf8_icelandic_ci` DESC, `num_keyword` 
LIMIT 15) AS `table`
ORDER BY 
`keyword` COLLATE `utf8_icelandic_ci` ASC, `num_keyword

5.4222950935364

SELECT `keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM `ds_1_headword` 
WHERE `keyword` COLLATE `utf8_icelandic_ci` > 'Patagónía'
ORDER BY `keyword` COLLATE `utf8_icelandic_ci` ASC, `num_keyword` limit 15 

7.3961861133575

如果我没有记错的话，当列与查询中的排序规则相同时，这是多余的信息，可以删除，希望也能使其更快。

SELECT * 
FROM (SELECT `keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM `ds_1_headword` 
WHERE `keyword` <= 'Patagónía' 
ORDER BY `keyword` desc, `num_keyword` limit 15) AS `table`
ORDER by `keyword` ASC, `num_keyword

<5.4222950935364

SELECT `keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM `ds_1_headword` 
WHERE `keyword` > 'Patagónía'
ORDER BY `keyword` ASC, `num_keyword` limit 15 

<7.3961861133575

更新第1页的Eplain。查询

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    15  Using filesort
2   DERIVED     ds_1_headword   range   keyword     keyword     302     NULL    23205   Using where

解释2。查询

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  ds_1_headword   range   keyword     keyword     302     NULL    30646   Using where; Using filesort

Answer 1

如果您要查找速度，请将列keyword声明为CHARACTER SET utf8 COLLATION utf8_icelandic_ci。

通过在查询中声明不同的排序规则，就不能使用索引，因此您正在扫描整个表。

对于获得给定单词前后的15行(加上给定行)，这是最佳的：

( SELECT `keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM ds_1_headword
WHERE keyword <  'Patagónía'
ORDER BY keyword DESC LIMIT 15 )
UNION ALL
( SELECT `keyword`, `num_keyword`,`stem`, `gram_1_word_group` 
FROM ds_1_headword
WHERE keyword >= 'Patagónía'
ORDER BY keyword ASC  LIMIT 16 )
ORDER BY keyword;   -- assuming you want the result sorted

MariaDB 10.0.2具有"窗口化功能，在没有UNION的情况下可以做类似的事情，可能更有效

如果您需要通过各种排序规则对同一列进行排序，那么您就运气不好了。重新思考架构(添加更多列？)和/或需求(不需要多个排序规则？)。

Answer 2

如果你想让数据检索更快，这里有几个广泛的主题应该让你朝着正确的方向开始：

1. 数据库是否运行在"足够强大"的机器上？对于一个53K的行表来说，即使是一台有1核和4GB RAM的机器也"足够强大"，除非机器被其他工作负载淹没。

2. 如果机器"足够强大"，那么您应该查看mysql表并检查执行计划(如果需要，请查找)。您需要做的一件非常常见的事情是检查您正在搜索/排序的列上是否有INDEX(在您的情况下为keyword)。以下是mysql 中的索引链接

3. 如果让数据库更快变得困难，你可以将ALL数据拉入PHP，如果执行PHP的机器"足够强大"，脚本应该能够管理53K值，并在几毫秒内(而不是几秒钟)拉出"之前15，之后15"。至于如何在PHP中做到这一点，这里有一个从表中选择的PHP教程页面的例子。

希望这能让你走上正确的道路。如果你还需要帮助，就去问吧！