CODEWITHSUNDEEP
phpmysql
Tharindu Gallage
Tharindu Gallage

Reputation: 27

mysql - working with big table 200k rows

i'm using this code to genarate html table from mysql tabe. table has 200k rows. 

$view->ach = $db->Query("SELECT from_unixtime(`date`), `aav_id`, `aav_domain`, `aav_word`, `aav_referer`, `aav_ip`, `aav_country` 
FROM aav_views 
where aav_user_id=$USER_ID
ORDER BY date DESC
");

but it's not working. web browser saying

"The page isn’t working www.mysite.com is currently unable to handle this request. HTTP ERROR 500 "

(not the 500 internal server error)

i add a limit to sql query like this

$view->ach = $db->Query("SELECT from_unixtime(`date`), `aav_id`, `aav_domain`, `aav_word`, `aav_referer`, `aav_ip`, `aav_country` 
FROM aav_views 
where aav_user_id=$USER_ID
ORDER BY date DESC
LIMIT 1000
");

now it is working fine. but i need to use without limit. i need to query all 200k rows

Upvotes: 2

Views: 1665

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

The way to handle such a large result set from MySQL is to use something called pagination. With pagination, you might only retrieve records 100 or 1000 at a time. This eliminates the problem of crashing your web server or web page with too much information.

MySQL has two keywords which are well suited to handle this problem. The first one, LIMIT, you already know, and it controls how many total records appear in the result set. The second one is OFFSET, and it specifies the position in the result set from which to begin taking records.

To give you an example, if you wanted to return the second 100 records from your table, you would issue the following query:

SELECT from_unixtime(date), aav_id, aav_domain, aav_word, aav_referer, aav_ip, aav_country
FROM aav_views 
where aav_user_id=$USER_ID
ORDER BY date DESC
LIMIT 100 OFFSET 100

Typically, the user controls the offset by paging through a UI containing the results from the query.

Upvotes: 1

Related Questions