Dealing With Lots Of Data

[dannon]

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 )

[Jessica]

You shouldn't be selecting 4 million rows to process them with PHP. You would need to create a better query.

[Barand]

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 October 14, 2012 by Barand

[dannon]

Nevermind.

Edited October 14, 2012 by dannon

[Jessica]

Post your table structure and sample data, and the query you used.

[dannon]

Post your table structure and sample data, and the query you used.

 

The query was working fine, sorry.

 

Thank you for your help!