How to get the closest matches first from MySQL-CodePudding

I have a table of 10 million records and and am trying to select user details like firstname, lastname and country. I need to get results back in order where (order by column="abc") would give me results where those that match are ranked on the top.

what I have tried

Query one

-- this is match slower with 45  seconds
select firstname, lastname, town
from user_db
order by town="abc" DESC
limit 25;

Query two

-- much faster with 0.00019 seconds
select firstname, lastname, town
from user_db
order by town DESC
limit 25;

The problem

The first query also works but takes 45 seconds while if I remove the equals expression in the (order by clause) like in the second query, it's match faster. And obviously I do use where clauses but this is a simplified example.

other notes

There are currently no joins on the query as it is just a simple select statment of user details and my setup is pretty good with 30GB ram and 2TB of storage all local.

Indexes: All columns mentioned have indexes but the (order by town="abc") clause triggers a full table search and as a result, this ends up finishing in 2 minutes

Is there a way to get results ranked by closest matches first faster within a single query? Any help will gladly be appreciated. Thank you.

CodePudding user response：

It looks to me like your user_db table has an index on your town column. That means ORDER BY town DESC LIMIT 25 can be satisfied in O(1) constant time by random-accessing the index to the last row and then scanning 25 rows of the index.

But your ORDER BY town='abc' DESC LIMIT 25 has to look at, and sort, every single row in the table. MySQL doesn't use an index to help compute that town='abc' condition when it appears in an ORDER BY clause.

Many people with requirements like yours use FULLTEXT searching and ordering by the MATCH() function. That gets a useful ordering for a person looking at the closest matches like in the searching location bar of a web browser. But don't expect Google-like match accuracy from MySQL.

CodePudding user response：

One way is to add a new column that has a value of country="abc", then sort by this column.

I'm rebuilding my workspace right now so I cannot try it properly, but something like:

select firstname, lastname, town, town="abc" as sortme
from user_db
order by sortme desc, town, lastname, firstname
limit 25;

CodePudding user response：

You can decouple the query into two queries each one being very fast.

First, create an index on town.

create index ix1 on user_db (town);

Then get the matches, with a limit of 25 rows:

select * from user_db where town = 'abc' limit 25

The query above may return any number of rows between 0 and 25: let's call this number R. Then, get the non-matches:

select * from user_db where town <> 'abc' limit 25 - R

Assemble both result sets and problem solved. Even if the second query results in a table scan, it will be concluded earlier resulting in a low cost.