Deleting duplicate rows crashes the mysql server

Databases
1

I did some research online and found the following query. I tried it on a small test table, and verified that it works well.

[COLOR=#2862C9][FONT=Menlo]DELETE[/FONT][/COLOR][FONT=Menlo] s1 [/FONT][COLOR=#2862C9][FONT=Menlo]FROM[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#884FFF][FONT=Menlo]searches[/FONT][/COLOR][FONT=Menlo] s1, [/FONT][COLOR=#884FFF][FONT=Menlo]searches[/FONT][/COLOR][FONT=Menlo] s2 [/FONT][COLOR=#2862C9][FONT=Menlo]WHERE[/FONT][/COLOR][FONT=Menlo] ([/FONT][COLOR=#25238E][FONT=Menlo]DATE_SUB[/FONT][/COLOR][FONT=Menlo]([/FONT][COLOR=#25238E][FONT=Menlo]CURDATE[/FONT][/COLOR][FONT=Menlo](),[/FONT][COLOR=#25238E][FONT=Menlo]INTERVAL[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#A42900][FONT=Menlo]1[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#25238E][FONT=Menlo]DAY[/FONT][/COLOR][FONT=Menlo]) <= s2.[/FONT][COLOR=#25238E][FONT=Menlo]timeStamp[/FONT][/COLOR][FONT=Menlo]) [/FONT][COLOR=#2862C9][FONT=Menlo]AND[/FONT][/COLOR][FONT=Menlo] s1.[/FONT][COLOR=#30A500][FONT=Menlo]uID[/FONT][/COLOR][FONT=Menlo] = s2.[/FONT][COLOR=#30A500][FONT=Menlo]uID[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#2862C9][FONT=Menlo]AND[/FONT][/COLOR][FONT=Menlo] s1.[/FONT][COLOR=#30A500][FONT=Menlo]keyword[/FONT][/COLOR][FONT=Menlo] = s2.[/FONT][COLOR=#30A500][FONT=Menlo]keyword[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#2862C9][FONT=Menlo]AND[/FONT][/COLOR][FONT=Menlo] s1.[/FONT][COLOR=#30A500][FONT=Menlo]alerts[/FONT][/COLOR][FONT=Menlo] < s2.[/FONT][COLOR=#30A500][FONT=Menlo]alerts[/FONT][/COLOR][FONT=Menlo];[/FONT]

When I tried it on a larger table with over 300,000 rows, I got the following error:

"The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay "

I am able to run the query once I execute, “SET SESSION SQL_BIG_SELECTS=1;”, however, after ten minutes it brings down the mysql server. Not a fun thing. Any ideas on how I can make this work? I thought using DATE_SUB to only get rows modified in the last day would help, but it doesn’t.

Thanks!

2

For the date part of the query, is your intention to select any search record that is older then 1 day or any that are less then one day old?

Is there meant to be just one searches table or two?

Can you please post the output of a SHOW CREATE TABLE query for the tables concerned and some sample data?

3

It’s my intention to find possible duplicates of any rows that have been modified in the last day. I removed the UNIQUE index so that I could do mass corrections on mis-spelled keywords and I plan on re-adding the index once I figure out a good way to eliminate the duplicates. Keep in mind that this worked fine on a very small test table.

There is only one table involved here, called searches. Here is the info for creating the table:

CREATE TABLE searches (
sID int(11) unsigned NOT NULL AUTO_INCREMENT,
searchDate datetime DEFAULT NULL,
uID int(11) unsigned NOT NULL DEFAULT ‘0’,
type varchar(8) DEFAULT NULL,
keyword varchar(35) DEFAULT NULL,
alerts smallint(3) unsigned DEFAULT ‘0’,
lastAlert datetime DEFAULT NULL,
clicks smallint(3) unsigned DEFAULT ‘0’,
lastClick datetime DEFAULT NULL,
timeStamp timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (sID),
FULLTEXT KEY keyword (keyword)
) ENGINE=MyISAM AUTO_INCREMENT=984875 DEFAULT CHARSET=latin1;

Here is what a sample row contains:

sID, searchDate, uID, type, keyword, alerts, lastAlert, clicks, lastClick, timeStamp

983664
2013-02-24 13:06:33
717
basic
lavender oil
12
2014-04-22 13:06:33
2
2014-04-24 12:12:13
2014-04-25 13:06:33

Thank you!

4

Time to call in the big guns. Rudy, where art thou?

5

monitoring all the threads in this forum, letting the ones intended for DBAs go sliding right by :slight_smile:

stuff that works fine on a small table but chokes on a large one – that’s definitely a job for a DBA

did i mention i’m not a DBA?

6

LOL. Ok, what are you so I know for future reference?

7

SQL consultant

i know how to write the sql, to get what you want returned from the database

i am also, if i might say so myself, a dab hand at database design

8

Ok, can you point me in the right direction so I can figure out why this query works fine on a small table but not one with over 300,000 rows?

Thanks!

9

optimize the server… increase the buffer pool… adjust the mysql parameters… add some ram… adjust the fluxinator and reset the framistan

10

check the error log and find why mysql crashed, you may also need a key on column timestamp