busboy
April 24, 2014, 7:38pm
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!
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?
busboy
April 25, 2014, 3:13pm
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!
busboy
April 28, 2014, 10:59pm
4
Time to call in the big guns. Rudy, where art thou?
r937
April 29, 2014, 3:49am
5
monitoring all the threads in this forum, letting the ones intended for DBAs go sliding right by
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?
busboy
April 29, 2014, 2:21pm
6
LOL. Ok, what are you so I know for future reference?
r937
April 29, 2014, 4:42pm
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
busboy
April 29, 2014, 5:06pm
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!
r937
April 29, 2014, 6:33pm
9
optimize the server… increase the buffer pool… adjust the mysql parameters… add some ram… adjust the fluxinator and reset the framistan
check the error log and find why mysql crashed, you may also need a key on column timestamp