Phpmyadmin freezes on query, db too big?

Question

I got two tables (one with 33k rows and another with 7k rows). I want to compare the two tables and delete the rows in which the two id's dont match. But when i send off the query, it freezes phpmyadmin. Could the tables be too big?

SELECT * FROM likes LEFT OUTER JOIN uploads on likes.upload_id = uploads.upload_id WHERE uploads.upload_id IS NULL

I know databases are meant to handle millions of data, so i'm not sure where the error lies.

Best regards, Mathias

when phpmyadmin freezes, the script is being executed. Are you sure you have indexes in place? 33k rows is nothing, should not be an issue. — Rene Pot, Apr 13 '12 at 08:22
Cursor looks as if it's working, but nothing happens. Pretty sure i got the indexes in place, yeah — Mathias Fyrst Jakobsen, Apr 13 '12 at 08:23

score 3 · Accepted Answer · edited Apr 13 '12 at 08:25

3

I would do an explain so you can see what mysql does. This would give a good indication on what how many rows are used.

EXPLAIN SELECT * FROM likes LEFT OUTER JOIN uploads on likes.upload_id = uploads.upload_id WHERE uploads.upload_id IS NULL

you could also use another browser or another session to use show processlist to see how the query is executed.

edited Apr 13 '12 at 08:25

Rene Pot

24,681
7
68
92

answered Apr 13 '12 at 08:24

Iggy Van Der Wielen

124
6

score 2 · Answer 2 · edited May 23 '17 at 12:15

2

Tables probably aren't too big but PhpMyAdmin's query timeout might be an issue if the script takes a long time to finish. Try and change the timeout or don't use PhpMyAdmin at all. See if you are able to so the query using command line on the server (or some other tool).

See also:

edited May 23 '17 at 12:15

Community

1
1

answered Apr 13 '12 at 08:38

ZZ-bb

2,157
1
24
33

score 0 · Answer 3 · answered Dec 19 '14 at 10:33

Does Freeze imply it's working but never updates/ never "finishes" the task?

If so,
I have had a similar thing and found they've been due to having more than one index on a column, for example a column has a PRIMARY index and an index named after the column, so check your indexes

score 0 · Answer 4 · edited May 23 '17 at 11:59

I sometimes run large import "queries" that can take a few hours to process. (I untick "Partial import -> Allow the interruption of an import...".)

phpMyAdmin will not respond in the browser I started the queries in, even if I try to reload the page, but if I go to a browser on another machine with (I assume) a different IP address phpMyAdmin responds fine.

On the other browser I then issue the SQL command SHOW FULL PROCESSLIST to monitor and kill that long running query if need be.

score 0 · Answer 5 · answered Oct 03 '21 at 05:29

you can just uncheck to two option in import

1 - Allow the interruption of an import in case the script detects it is close to the PHP timeout limit. (This might be a good way to import large files, however it can break transactions.)

2- Enable foreign key checks

Phpmyadmin freezes on query, db too big?

5 Answers5

Linked