Force index using the unused index columns having effect as order by for the result set?

Question

if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 automatically?

Table is something like this:

CREATE TABLE IF NOT EXISTS `jobs` (
    `job_id` varchar(36) NOT NULL,
    `job_status` varchar(30) NOT NULL,
    `created_at` datetime NOT NULL,
    `lease_date` datetime,
    `priority` int NOT NULL,
    PRIMARY KEY(`job_id`),
    INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The full query I want to do is the following:

select * from jobs use index (job_status_priority_lease_date)
where status="IN_PROGRESS"
and priority=0
limit 100

Would my query result be sorted by the lease_date automatically? Because of some limitation, I cannot use the order by statement.

Answer 1

There is no guarantee of any order without an explicit ORDER BY clause. This is a fundamental aspect of SQL which deals with sets. Sets, by definition, have no order. An "Ordered Set" is no longer a set, it is a cursor. Even if you happen to see rows come back in a certain order repeatedly due to indexes or execution plan operators, you should never assume it will always be that way. I've seen many application fail due to making such assumptions.

I'm not sure what 'some sort of limitation' prevents you from using ORDER BY, but as a general rule, and since the order in this case is only for presentation purposes only, you should consider ordering the rows at the client side instead of at the database. The client (or presentation tier) is responsible for presentation aspects such as order, fonts, colors, indentations etc.

Also note that your LIMIT clause can pick any 100 arbitrary rows without an ORDER BY. Don't expect the LIMIT to pick up the first 100 rows based on some index order.

HTH.