CODEWITHSUNDEEP
mysqlsql
Meera Joshi
Meera Joshi

Reputation: 59

Query Limit from MYSQL

I have database with about 500K row in it. I want get random row from row like 1 to 5000 and its limit result limit need like 100.

My current query is like below 

'SELECT * FROM user where status='0' LIMIT 10,100'

what should I change or use for get limited random row, so I can get fast result without memory consume ?

Thanks

Upvotes: 2

Views: 549

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

A database table is an unordered set, so you'll have to provide some order to get 1 to 5000 rows (otherwise those will be any 1 to 5000 rows), may be based on userid.

Once you have that, you can limit the rows in subquery and sort by rand() and get first 100 like this:

select *
from (select
        *
    from user
        where status = 0
    order by /* set of columns, may be user_id*/
    limit 1, 5000
) t order by rand() limit 100;

Upvotes: 3

Mack4Hack
Mack4Hack

Reputation: 119

This query gives you any 100 random rows from your 5000k rows

select * from user where status='0' order by rand() limit 100

Upvotes: 0

Related Questions