Index Question

MySQL & MariaDB Other MySQL® Questions
1

I have following index question:

First a have this table definition:

mytable

  • id (pk)
  • column1 (fk to any other table)
  • column2
    index 1: id
    index 2: column1

…when a call this query, then it is not very fast because only one column index is used
select * from mytable where column1 = ?? order by column2

…then a changed mytable to this:
mytable

  • id (pk)
  • column1 (fk)
  • column2
    index 1: id
    index 2: column1, column2

in this table I created index with both columns and droped column1 index

and now my question: is there any other performance problem when a do this?

2

Since you have only one where clause. It okay to index just one column(primary key).

Sort by happens after the query retrieval and this doesn’t depend on the index on foreign key column2.

Hence it wise to just have one index, unless you join with another table.

Regards
Shobana