MySQL server has gone away - after a mysql php loop

Question

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

If you just execute the query with out the "craete arrays" section do you still get timeouts errors? — AllInOne
– AllInOne, Commented Sep 30, 2012 at 0:34
if you are trying to get a count of likes wouldn't it be more efficient to do that as part of the query instead of in PHP? Why bring all 20MM records into PHP when you can just ask MySQL for a COUNT... WHERE... ? — AllInOne
– AllInOne, Commented Sep 30, 2012 at 0:37
i dont know to do it in mysql... i need to count grouped by name and siplay only if the count is higher than 375 — Dan Stern
– Dan Stern, Commented Sep 30, 2012 at 2:37
Does this answer your question? Reconnecting on MySQL Server Has Gone Away — Moradnejad
– Moradnejad, Commented Mar 10, 2020 at 10:35

air4x · Accepted Answer · 2012-09-30 03:45:01Z

1

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

answered Sep 30, 2012 at 3:45

air4x

5,6831 gold badge26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Stern Over a year ago

when using this appreach i receive: Lost connection to MySQL server during quer.... the db has over 20 million rows, so probably it takes too long and the connection is closed

air4x Over a year ago

@DanStern try adding an index for the name field. If possible, use an id field instead of name for doing the grouping.

Ja͢ck · Accepted Answer · 2012-09-30 04:20:29Z

1

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.

answered Sep 30, 2012 at 4:20

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

2 Comments

Dan Stern Over a year ago

thats the problem.. when using this appreach i receive: Lost connection to MySQL server during quer.... the db has over 20 million rows, so probably it takes too long and the connection is closed

Ja͢ck Over a year ago

@DanStern I suppose you could test the speed separately using a MySQL command line client, also use the EXPLAIN feature to find out how you could optimise it, though I doubt you can optimise it much in this way.

Case · Accepted Answer · 2012-09-30 00:17:32Z

0

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.

answered Sep 30, 2012 at 0:17

Case

4,2946 gold badges37 silver badges55 bronze badges

Comments

AllInOne · Accepted Answer · 2012-09-30 00:43:25Z

0

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.

answered Sep 30, 2012 at 0:43

AllInOne

1,4502 gold badges14 silver badges32 bronze badges

1 Comment

Dan Stern Over a year ago

actually im not counting for name=375, im checking how many times each name appears. If appears over 375 times the counts goes one up

Collectives™ on Stack Overflow

MySQL server has gone away - after a mysql php loop

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related