0

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.

  • if you want a particular order use 'ORDER BY" otherwise a future enhancement may not give you that order. You should avoid explicit force index directives. Use EXPLAIN {query} to show which index was used and how. – danblack Jun 11 at 23:10
  • No query wont be in lease_date sorted. – JYOTI RAJAI Jun 12 at 12:52
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.