MySql Indexes Sort and Where

Question

I would like to know how MySql handle the indexes priority. I have the following table.

   CREATE TABLE table (
       colum1 VARCHAR(50),
       colum2 VARCHAR(50),
       colum3 ENUM('a', 'b', 'c'),
       PRIMARY KEY(colum1, colum2, colum3)
   );

   CREATE INDEX colum1_idx ON table (colum1);
   CREATE INDEX coloum2_idx ON table (colum2);


    const query = `SELECT * FROM table
                    WHERE colum1 = ?
                    ORDER BY colum2
                    LIMIT ?,?`;

Basically my PK is composed by all fields (I need to use INSERT IGNORE) and I am query using colum1 as WHERE clause and ORDER by colum2.

My question is should I create 2 different indexes or create 1 index with (colum1 and colum2)?

MySQL index [**TIPS**](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) — Juan Carlos Oropeza, May 30 '17 at 16:02
Thanks @JuanCarlosOropeza I found my answer here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#algorithm_step_2c_order_by_ — Marco Talento, May 30 '17 at 16:06

score 0 · Accepted Answer · answered May 30 '17 at 16:08

0

Thanks to @JuanCarlosOpo

I find the answer here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#algorithm_step_2c_order_by_

It's more performant using a compound index using both columns.

CREATE INDEX colum_idx ON table (colum1,colum2);

Thanks a lot!

answered May 30 '17 at 16:08

Marco Talento

2,335
2
19
31

1

You should always test your query using explain plan. [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) – Juan Carlos Oropeza May 30 '17 at 16:11
Thanks a lot for your time, I will try that! – Marco Talento May 30 '17 at 16:20

MySql Indexes Sort and Where

1 Answers1