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 3rdSeq_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:
Is it a good (performance) practice to insert
ID
in the composite index? as it is only used for ORDER BYIs it okay to use ID (primary key) as the third sequence in index while it is first column on the table
Did i choose my Index correctly based on my sample query?
Edit:
I use InnoDB