If I have a PK int column, named Id and I perform a query like this: SELECT * FROM MyTable WHERE ID LIKE '123%'; it should use the PK index.
I replied: No. But I wanted to find some hard proof of it.
So I just fired MariaDB and shoot the following commands:
Here the script so far: MariaDB - Create database. I have provided only MariaDB script, since it should work in both sides.
Let's go OfficialThis is the official mysql indexes documentation: https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
So MySQL says:
Most MySQL indexes (
FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that
MEMORYtables also support hash indexes.
so those a primary key so far is a B-TREE. And now let's look what it says about the B-TREE and like queries the doc:
B-Tree Index CharacteristicsSELECT * FROM
key_colLIKE 'Patrick%'; SELECT * FROM
key_colLIKE 'Pat%_ck%';SELECT * FROM
key_colLIKE '%Patrick%'; SELECT * FROM
and so far the Primary Key index will use the index. Since our query states:
ID LIKE '123%'
Let's do the testI am gonna create the following table:
And of course the script: Create Table.MariaDB.sql
And insert the data, by using LOAD DATA
Finally the Query
keycolumn indicates the key (index) that MySQL actually decided to use. The key is
NULLif no index was chosen.
possible_keyscolumn indicates which indexes MySQL could use to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from
EXPLAIN. That means that some of the keys in
possible_keysmight not be usable in practice with the generated table order.