MySQL – Select From Certain Record To The Last Record


To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 96, 18446744073709551615;

 

18446744073709551615 is 2^64-1 for those who were wondering. You may want to watch out because you won’t be able to store this value in an 32 bit integer. You have to make sure you store this as a string to ensure compatibility

18446744073709551615 is the maximum value of unsigned BIGINT

 

source:

http://stackoverflow.com/questions/255517/mysql-offset-infinite-rows

Friend of Friend


———-
UID | FID
———-
1 | 2
1 | 3
1 | 4
2 | 4
4 | 5
Dst…

UID: User ID
FID: Friends ID

—————————————————————————–

join similar table

select u1.uid as people, u2.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

——————————————————–

select u1.uid as people, u3.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
inner join tabel u3
on u2.fid=u3.uid
where u1.uid=1;

—————————————————————————–
search whose uid=1 :

SELECT fid FROM t WHERE uid=1

search friend of friend whose uid=1 :

SELECT fid FROM t WHERE uid IN (SELECT fid FROM t WHERE uid=1)

or (maybe) :

SELECT DISTINCT t2.fid FROM t t1
INNER JOIN t t2 ON t2.uid=t1.fid
WHERE t1.uid=1

merge friend + friend of friend, use UNION.
—————————————————————————–

display it like friendster do :

select u1.uid as first, u1.fid as second, u2.fid as third
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

—————————————————————————–

1 level: (result: You > XXX > Target)

$sql=”SELECT friend_2.uid as XXX
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
WHERE
friend_3.uid=$uid and
friend_2.fid=$fid and
limit 1″;
“);

2 level: (result: You > XXX > YYY > Target)

$sql=”SELECT friend_2.uid as XXX, friend_1.uid as YYY
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
INNER JOIN friend_1
ON friend_2.fid = friend_1.uid
WHERE
friend_3.uid=$uid and
friend_1.fid=$fid and
limit 1”;

note: all friend table friend_1, teman_2, friend_3 has 75.000 rows,

problem:

1.very slow especially for 2 level
2. added with order by rand() sometimes time-out
3. how to make it simpler?

—————————————————————————