CODEWITHSUNDEEP
mysqlsql-like
luklapp
luklapp

Reputation: 205

MySQL: Default ordering in LIKE queries?

I have a MySQL table for users, which looks like this (part of it):

Now I want to get all users, which contain "foo". I use the following query:

SELECT user_id, user_name FROM users WHERE user_name LIKE '%foo%'

Question: How are the results of LIKE-queries ordered by default, when I don't have any ORDER BY clause?

Upvotes: 1

Views: 38

Answers (1)

Rowland Shaw
Rowland Shaw

Reputation: 38128

If no ORDER BY clause is specified, then the order is indeterminate.

In the simple case of selecting from a single table, were indexes can't be used (so doing (column) LIKE '%(something)' stops the use of any index on column), the results will tend to be in table order, which, unless you've manually reordered the table, will be the order they were added to the table.

If an index can be used (e.g doing something like (column) LIKE '(something)%' on an indexed column), the result may come back in the order of the index.

Adding joins to other tables makes it even more indeterminate, as you're also at the whimsy of any indexes used for the join condition.

Upvotes: 1

Related Questions