mysql - How to query a table with over 200 million rows? -

i have table users 1 column user_id. these ids more 200m, not consecutive , not ordered. has index user_id_index on column. have db in mysql , in google big query, haven't been able need in of them.

i need know how query these 2 things:

1) row number particular user_id (once table ordered user_id)

for this, i've tried in mysql:

set @row := 0; select @row := @row + 1 row users user_id = 100001366260516; 

it goes fast returns row=1 because row counting data-set.

select user_id, @row:=@row+1 row (select user_id users order user_id asc) user_id = 100002034141760 

it takes forever (i didn't wait see result).

in big query:

select row_number() over() row, user_id  (select user_id users.user_id order user_id asc) user_id = 1063650153 

it takes forever (i didn't wait see result).

2) user_id in particular row (once table ordered user_id)

for this, i've tried in mysql:

select user_id users order user_id asc limit 150000000000, 1  

it takes 5 minutes in giving result. why? isn't supposed fast if has index?

in big query, didn't find way because limit init, num_rows, doesn't exist.

i order table in new one, , add column called rank orders user_id, index on it. mess if want add or remove row.

any ideas on how solve these 2 queries?

thanks, natalia

for (1), try this:

select count(user_id) users user_id <= 100001366260516; 

you can check explain, should doing scan of index.

for (2). question: "why? isn't supposed fast if has index?". yes, use index. has count row 150,000,000,000 using index scan. hmmm, being end of table (if not typo). in case, index scan quite different doing index lookup, fast. and, take time. , more time if index not fit memory.

the proper syntax row_number(), way, be:

select row, user_id  (select user_id, row_number() on (order user_id) row       users.user_id ) user_id = 1063650153; 

i don't know if faster, @ least not explicitly ordering rows first.

if these types of queries need do, think way include ordering information column in table.