Call my on Skype Follow me on Twitter Join me on Facebook Connect with LinkedIn Hire me on UpWork
Richard Wallman - a MySQL® DBA (database administrator)


Blog: Things you probably don't need to do: FLUSH PRIVILEGES
(2018-07-05 11:47:12)
Blog: Why isn't MySQL using the index I expect?
(2018-06-06 10:05:48)
Going to change my policy towards NDAs - I have no problem with them, unless they gag me and create empty spaces in my work history
(2017-08-20 14:27:45)
My week in tags: #cpp #VideoStreaming #handlebarsjs #mongodb #español #holiday #family #perl #nda
(2017-07-02 22:40:00)
Lesson of the week: #MongoDB update() is not the same as SQL UPDATE. It's okay - I didn't need that document anyway 😉#rtfm
(2017-06-25 20:47:31)
My week in tags: #MEAN #MongoDB #JavaScript #NodeJS #expressjs #mustache #PHP #ceph #debian #cpp #html5 #VideoStreaming #fpga #verilog
(2017-06-25 20:38:53)
That was "fun" - setting up new @Percona #MySQL cluster, initial SST kept failing. Culprit: "skip-name-resolve" makes sstuser@localhost fail
(2017-06-20 22:17:45)
Hoping to finally find some time to have a quality play with @percona #MongoDB this weekend - only tested successful install so far
(2017-06-17 15:45:26)
Starting to clear old contracts/NDAs - been quite a year, doing coding, providing training, working with NoSQL databases. Full stack indeed!
(2017-04-21 21:32:02)
Just read the MySQL/HAProxy tutorial on @digitalocean - it's so awful it literally took my breath away for a second. Top result in Google :(
(2016-04-26 15:02:46)

Question from Twitter: why isn't MySQL using the index I expect?

Posted on 2018-06-06 @ 10:00:00

I recently received this question from someone on Twitter:

Hi Richard I couldn't find answer to my doubt..I searched all over the internet..does MySQL always use clustered index(primary key) for select * from table?

I had a composite primary key of 3 cols col1 col2 col3 and there was a foreign key on col4 ,MySQL chose to order by foreign key when I did select *

I wasn't provided with any more context, so I'm having to make the following assumptions:

  • They're using InnoDB
  • They're doing a table join (using the foreign key), or at least have a WHERE condition on the foreign key value
  • They're using at least MySQL 5.6
  • They had no explicit ORDER BY in their SQL query

The answer can be found in the MySQL manual on InnoDB index types:

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

What is most likely happening in this case is that the MySQL query optimiser is recognising that the secondary index contains the primary key as well as the foreign key field. This makes the secondary index more suitable for use (given the join or WHERE condition) as the table data would not need to be read for each row to get the foreign key field - we have an index scan rather than a table scan.

If there is no explicit ORDER BY clause in the SQL, it will sort results by the order of the index used - in this case, that's (col4,col1,col2,col3). This is why the results appear to be sorted by foreign key, rather than primary key, and serves as a good reminder that if you need a result set sorted in a particular order, you should always explicitly declare it.

Comments from visitors

Leave your own comment