Should we include sort column, primary key on composite index (MySQL)

Question

Table (Simplified):

+---------------------------------------------------------------------+
| id (Primary AI) |  user_id  |  status  |  type  |   data   |   ip   |
+=====================================================================+
|        1        |     3     |    0     |   abc  |   a-s-d  |    -   |
+---------------------------------------------------------------------+
|        2        |     1     |    0     |   ooo  |   z-z-z  |    -   |
+---------------------------------------------------------------------+
|        3        |     3     |    0     |   ooo  |   f-f-f  |    -   |
+---------------------------------------------------------------------+
|        4        |     2     |    0     |   abc  |   h-h-h  |    -   |
+---------------------------------------------------------------------+
|        5        |     1     |    0     |   abc  |   a-s-d  |    -   |
+---------------------------------------------------------------------+

More Info:

ID is the Primary Key of this table (Auto Increment)

Please Note that i have used ID (Primary Key) as the 3rd Seq_in_index on new index

I have created a Composite Index for the mentioned table

CREATE INDEX userid_type_id ON table (user_id, type, id);

The id in this Index is only used for sorting.

Sample Query

SELECT id, status, data, ip 
                      FROM `table`
                      WHERE user_id=3 AND type='abc' 
                      ORDER BY id DESC;

My questions are:

1. Is it a good (performance) practice to insert ID in the composite index? as it is only used for ORDER BY

2. Is it okay to use ID (primary key) as the third sequence in index while it is first column on the table

3. Did i choose my Index correctly based on my sample query?

Edit:

I use InnoDB

Answer 1

Answer depends on engine you use:

• MyISAM - adding id to index can and probably will help
• InnoDB - Primary Key is already part of each secondary index, because innodb stores rows in BTREE sorted by primary key and index needs primary to point to actual row - in that case adding it is redundant if it is last in the index (but doing so won't add it twice, so it should not make things worse). In some cases you may want to add it as not-last, or you have multicolumn primary and you add some columns to your index in different order - there should be no problem with it, innodb will append remaining columns of primary to that index, but can use those added before without duplicating them)

So answers:

1. In InnoDB it is unnecessary, In MyISAM it is good in case you actually use that sorting, if you dont use it, adding it only makes that index bigger.
2. Order of columns in the table definition and order in index are separate things, so it is OK
3. Yes, that index seems really good - but you can check yourself using EXPLAIN, there is possibility of even better performance - "covering index", but that comes with a cost so unless the query is critical and underperforming, it is probably overkill.