MySQL and Pagination

Question

Below is the table called list:

 Column Name   Data Type
   id             (Int)     
   user_one     (String)    
   user_two     (String)

I would like to scan for a user in user_one and user_two column.

In return I would like to get back the first 100 records and a flag that says there are more records.

Solution #1:

ALTER TABLE list ADD FULLTEXT KEY `full_name` (`user_one`,`user_two`);
SELECT * FROM list WHERE MATCH(user_one, user_two) AGAINST ('john');

I believe solution 1 will perform better, but I am not sure how to add a limit of 100 and get a flag.

Solution #2:

SELECT * FROM list 
WHERE "john" LIKE Concat(Concat('%',user_one),'%') 
OR "john" LIKE  Concat(Concat('%',user_two),'%')
ORDER BY id
LIMIT 50

I think solution #2 is slower, and I don't know what's the best way to add a flag if there are more than 100 records.

Last, if possible, I would like to minimize entire table scans.

Answer 1

1) You can get 101 Rows and only use 100. if your array bigger than 100 there must be more rows

SELECT * FROM list WHERE MATCH(user_one, user_two) AGAINST ('john') LIMIT 101;

2) Use SQL_CALC_FOUND_ROWS in your query

SELECT SQL_CALC_FOUND_ROWS * FROM list WHERE MATCH(user_one, user_two) AGAINST ('john') LIMIT 100;

SELECT FOUND_ROWS();

Get total number of rows when using LIMIT?