索引 varchar 列以搜尋第一個字母

我正在嘗試優化一個舊版網路應用程序，使用者可以在其中詢問名稱以給定字元開頭的所有記錄的列表。

要優化的查詢是：

SELECT oggetto.id_oggetto, titolo, anno, specifiche from oggetto inner join oggetto_formato on (oggetto.id_oggetto = oggetto_formato.id_oggetto)
           where attivato=1 and oggetto.titolo like 'C%' and id_formato IN ('6', '7', '4') order by titolo LIMIT 650,50;

對不起意大利人…. oggetto表示項目，titolo是標題。我想加快 oggetto.titolo 的查找速度。

EXPLAIN EXTENDED 告訴我以下內容：

+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
| id | select_type | table           | type  | possible_keys                           | key             | key_len | ref                          | rows | filtered | Extra                       |
+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | oggetto         | ALL   | PRIMARY                                 | NULL            | NULL    | NULL                         | 42585|   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | oggetto_formato | ref   | PRIMARY,FK_oggetto_formato_2,id_oggetto | PRIMARY         | 4       | agorateca.oggetto.id_oggetto |    1 |   100.00 | Using where; Using index    |
+----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+

我創建了以下索引：

CREATE INDEX title_initial ON item (title(2));

上述查詢在創建索引後 EXPLAIN EXTENDED 的結果為：

   +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
   | id | select_type | table           | type  | possible_keys                           | key             | key_len | ref                          | rows | filtered | Extra                       |
   +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+
   |  1 | SIMPLE      | oggetto         | range | PRIMARY,iniziale_titolo                 | iniziale_titolo | 9       | NULL                         | 2880 |   100.00 | Using where; Using filesort |
   |  1 | SIMPLE      | oggetto_formato | ref   | PRIMARY,FK_oggetto_formato_2,id_oggetto | PRIMARY         | 4       | agorateca.oggetto.id_oggetto |    1 |   100.00 | Using where; Using index    |
   +----+-------------+-----------------+-------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-----------------------------+

誰能證實我的觀點，即索引設置正確並且正在發揮作用？

我不確定您使用的是什麼 DBMS，但總的來說是的。看起來您的索引正在做它應該做的事情。作為一般情況，如果沒有模式（_%

$$ $$等）左側的字元。 所以這將使用一個索引

oggetto.titolo like 'C%'

但這不能

oggetto.titolo like '%C'

您需要查看兩個字元組合的分佈情況。

你應該執行這個查詢：

SELECT IFNULL(TwoChar,'All Combinations'),RowCount FROM 
(
   SELECT LEFT(titolo,2) TwoChar,COUNT(1) RowCount
   FROM oggetto GROUP BY LEFT(titolo,2) WITH ROLLUP
) A
ORDER BY RowCount;

然後，查看具有最高 RowCount 的兩個字元鍵。

如果您可以快速查找這些鍵，那麼也可以快速查找所有其他鍵。

引用自：https://dba.stackexchange.com/questions/56550