I'm making a query over a database with over 20MM entries, that means im breaking the query into several smaller queries.
The problem is if I try to fetch the 20MM entries the page does not load and gets the notice: MySQL server has gone away, and displays a blank screen, with no title and content. However, if I fetch 5MM entries, the page does load correctly, and displays the content:
Here's my code
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
ini_set('memory_limit', '1000M');
for($n=0; $n<20000000; $n=$n+500000){
$m=500000;
$query = "SELECT * FROM user_likes LIMIT ". $n .",". $m;
//echo $query;
$result = mysql_query($query) or die(mysql_error());
// craete arrays
while($row = mysql_fetch_array($result)){
set_time_limit(0);
$like[$row['name']]=$like[$row['name']]+1;
if($like[$row['name']]==375) $likes375 ++;
}
}
// print the size
echo count($like)."<br>";
echo "375: ".$likes375;
I would appreciate if someone can help me with this. Thanks
Sounds like your backing it up and your going to have to wait for it to finish and you maximum execution time for php is shorter than its going to take for the query to finish. Suggestion is to do what you need to do in smaller chunks.
Couldn't you do this more efficiently on the MySQL side?
SELECT count(*) FROM user_likes where name = '375'
This will be much faster than your method. An index on name will make it faster still.
Use the below query
SELECT COUNT(*) AS likes375 FROM
(
SELECT COUNT(*) AS name_count
FROM user_likes
GROUP BY name
) counted
WHERE name_count > 375
This shows all names with a count of more than 375:
SELECT name
FROM user_likes
GROUP BY name
HAVING COUNT(*) >= 375
This is still pretty inefficient though, if there are 20M+ rows to go through; it might be better to keep this condensed data inside a smaller table.