CODEWITHSUNDEEP
mysql
someother
someother

Reputation: 21

Mysql query - Search only the latest x rows

This is my first question here so I hope im doing it right. I have the following SQL query:

SELECT *, 
  MATCH(name, descr) AGAINST ('$q') AS score 
FROM songs 
WHERE MATCH (name, descr) AGAINST('$q') 
ORDER BY score DESC

As you may know, this query selects everything from every row from the songs table, if there is a match in the description or in the name .What I want to do,is to limit the query only to search the latest 10k rows for example. I also have a primary key, id.

Upvotes: 0

Views: 227

Answers (2)

Eric Citaire
Eric Citaire

Reputation: 4513

You can ORDER BY score ASC (instead of DESC) and add LIMIT 10000 and finally reverse the order of the result :

SELECT * FROM (
  SELECT *, 
    MATCH(name, descr) AGAINST ('$q') AS score 
  FROM songs 
  WHERE MATCH (name, descr) AGAINST('$q') 
  ORDER BY score ASC
  LIMIT 10000
) ORDER BY score DESC

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

You will need a nested query. Try this:

SELECT temp.*, 
  MATCH(name, descr) AGAINST ('$q') AS score
FROM (
    SELECT *
    FROM songs 
    ORDER BY id DESC
    LIMIT 10000
    ) temp
WHERE MATCH (name, descr) AGAINST('$q') 
ORDER BY score DESC

Upvotes: 2

Related Questions