PHP times out and PHPmyadmin crashes on query with NOT IN

Question

I have two tables in a database. Table1 stores the users and Table2 stores data generated by the users.

Due to an error some users from Table1, don't have any data in Table2. I would like to notify these users.

I am trying this in several ways using MySQL. But nothing works, because PHPmyAdmin freezes or PHP times out. That's maybe because the first table contains 28.000 rows and the second one contains 80.000 rows. I just need the last 5 days.

Trial nr 1

SELECT gebruikers.g_id FROM gebruikers LEFT JOIN objecten ON objecten.o_g_id=gebruikers.g_id WHERE objecten.o_g_id IS NULL

EXPLAIN SELECT

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gebruikers index NULL PRIMARY 4 NULL 25227 Using index 1 SIMPLE objecten ALL NULL NULL NULL NULL 81002 Using where; Not ex

Trial nr 2

SELECT g_id FROM gebruikers WHERE g_id NOT IN(SELECT o_g_id FROM objecten WHERE o_startdatum <= NOW() AND o_startdatum >=NOW() - INTERVAL 5 DAY)

What am i doing wrong?

80,000 rows is a very small amount of data for MySQL to handle and it shouldn't be hanging up on this at all, unless it's running on a low end server. What type of field is o_startdatum? Is it DATE or DATETIME? If it's a date, do you have an index defined on this field? If it's a datetime - it may be better to create a DATE column (with just the date portion of this field) and index it. Also, how many results do you expect this to return? Maybe phpMyAdmin is choking up on outputting the data. — Colin M
– Colin M, Commented Jan 29, 2013 at 13:22
Please include the results of the explain query, EXPLAIN SELECT gebruikers.g_id... — Igor
– Igor, Commented Jan 29, 2013 at 13:23
@mat Yeah, definitely let us take a look at the EXPLAIN as mentioned by Igor. And also post the output of DESCRIBE on both of the tables. — Colin M
– Colin M, Commented Jan 29, 2013 at 13:34
try : SELECT gebruikers.g_id FROM gebruikers LEFT JOIN objecten ON objecten.o_g_id = gebruikers.g_id WHERE objecten.o_g_id IS NULL and o_startdatum <= NOW() AND o_startdatum >= (NOW() - INTERVAL 5 DAY); — जलजनक
– जलजनक, Commented Jan 29, 2013 at 13:42
@mat in phpmyadmin in the structure view of the table, on each of the mentioned columns hover over "more" then select "add index". Do this for each column individually. — dualed
– dualed, Commented Jan 29, 2013 at 14:35

जलजनक · Accepted Answer · 2013-01-29 13:54:50Z

Query to get required records:

SELECT gebruikers.g_id FROM gebruikers LEFT JOIN objecten ON objecten.o_g_id = gebruikers.g_id WHERE objecten.o_g_id IS NULL and o_startdatum <= NOW() AND o_startdatum >= (NOW() - INTERVAL 5 DAY);

Raviraj · Accepted Answer · 2013-01-29 13:28:52Z

"Trial nr 1" is fine if second table having null values but it might be field value empty instead of null so you should use or clause objecten.o_g_id = ''. The final query would be: SELECT gebruikers.g_id FROM gebruikers LEFT JOIN objecten ON objecten.o_g_id=gebruikers.g_id WHERE (objecten.o_g_id IS NULL) or (trim(objecten.o_g_id) ='')

Collectives™ on Stack Overflow

PHP times out and PHPmyadmin crashes on query with NOT IN

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related