Jump to content

Dealing With Lots Of Data


dannon

Recommended Posts

I am trying to learn how to deal with lots of data.. so I have inserted over 4 million rows into a MySQL table with an unique and with random numbers. I want to use php to find out a rank of an unique row based on the number of votes they get.

And when I retrieve the data from the MySQL database I obviously get the 'error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in ...' on line' error.

 

This is the code that I use to get the error:

$sth = $this->db->query("SELECT votes FROM list ");
       $this->toplistItems = $sth->fetchAll(PDO::FETCH_ASSOC);

 

How can I deal with large amounts of data?

And yes.. i know that I can increase the allocated memory.. but let's say that I can't.

 

Thank you in advance.

 

(Also posted on: http://www.phphelp.com/forum/index.php?topic=18402.0 )

Link to comment
Share on other sites

assuming your unique column is "id", the random values are "votes" and you want the ranking of id = 123456.

 

SELECT (COUNT(*) + 1) as rank
FROM bigtable
WHERE votes > (
   SELECT votes
   FROM bigtable
   WHERE id = 123456
   )

 

edit: an index on votes should speed it up

Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.