Improving the SQL commands to display preceding and following values in alphabetical order

Question

With the two following SQL commands I seek 15 headwords that preceding the displayed headword and 15 headwords that following the headwords in alphabetical order.

The keyword column is indexed. The time of execution is stated.

Is there quicker or better solution to find several preceding and following values in large alphabetical list?

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

If I am not mistaken, when the column is in the same collation as in the query, this is redundant information and can be deleted, and hopefully also makes it faster.

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

Update Eplain of 1. query

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

Explain of 2. query

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

If you are looking for speed, declare the column keyword to be CHARACTER SET utf8 COLLATION utf8_icelandic_ci.

By stating a different collation in the query, no index can be used, so you are scanning the entire table.

For getting the 15 rows before and after a given word (plus the given row), this is optimal:

( 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 has "Windowing functions, which can do a similar thing without the UNION, probably even more efficiently.

If you need to order the same column by a variety of Collations, you are out of luck. Rethink the schema (add more columns?) and/or the requirements (not require multiple collations?).