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:
IDis the Primary Key of this table (Auto Increment)Please Note that i have used
ID (Primary Key)as the 3rdSeq_in_indexon new index
I have created a Composite Index for the mentioned table
CREATE INDEX userid_type_id ON table (user_id, type, id);
The
idin 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
IDin 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