Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I do the following MySQL query

select * from table where name Like '%john%' limit 5;

that mean the query will search all the mysql database table and return only the top 5 results that match the query where condition.

BUT, what if want to just search the top 1000 records in the database table that match the where condition and return only the first 5 rows , because I have 1,000,000 records in the table and i just want to search in the first 1000 records, what should i use?

this is the wanted query in human readable:

search the top 1000 records from the table which have the name like john and return only the top 5 records that match.

what is the corresponding in MySQL

share|improve this question

migrated from stackoverflow.com Apr 17 '13 at 13:19

This question came from our site for professional and enthusiast programmers.

    
erm, what?..... –  hjpotter92 Apr 17 '13 at 8:08

4 Answers 4

up vote 3 down vote accepted

You could use something like this:

SELECT *
FROM (
  SELECT *
  FROM your_table
  ORDER BY ...
  limit 1000
) s
WHERE name LIKE '%john%'
ORDER BY ...
LIMIT 5
share|improve this answer
    
How is its performance better than what the OP is using right now? –  hjpotter92 Apr 17 '13 at 8:09

You can't do something like a 'first 1000' without an order, since each query could give you a different order if you don't specify it. So, let's try this way:

select * from (select * from table order by PUTYOURORDERFIELDHERE LIMIT 1000) where name    
like '%john%' limit 5;
share|improve this answer
1  
+1 for spotting that a LIMIT clause without an ORDER BY clause has little meaning. –  RandomSeed Apr 17 '13 at 8:10
    
sql execution have error while we must add a temp table nane like select * from (select * from table order by PUTYOURORDERFIELDHERE LIMIT 1000) t where name like '%john%' limit 5; –  Mohammed Shannaq Apr 17 '13 at 9:25

Use a subquery to extract the desired search range, then use it like a regular table:

SELECT search_range.*
FROM (
    SELECT * FROM table WHERE name LIKE '%john%' LIMIT 1000
) AS search_range
WHERE [extra conditions]
LIMIT 5;
share|improve this answer

You can try using the TOP The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Basic Usage:

SELECT TOP number|percent column_name(s)
FROM table_name

this will help you limit your search space as you said you want to search the top 1000 records from the table which have the name like john and return only the top 5

so your statement would be somewhere like

select top 1000 * into #temp from table
select * from #temp where name Like '%john%' limit 5;

this will help you in getting the desired results, as its first fetching the 1000 top records and then you will be searching on those 1000 records only .

** please dnt forget to drop the temp table

drop table #temp

Hope that solves you problem :)

share|improve this answer
    
mySQL does not have the select top statment –  Mohammed Shannaq Apr 17 '13 at 8:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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