How do I limit rows?

The script I'm using has a search tag, but I get a ton of searches a day, and it creates waayyyyy too many table rows. Is there a way to limit it, so say it only adds 1000 rows, one for each search, then it just deletes a row, and adds a new one?

Here's what was included with the script I purchased:

$tableSchema[] = "DROP TABLE IF EXISTS " . PREFIX . "_tag";
$tableSchema[] = "CREATE TABLE IF NOT EXISTS " . PREFIX . "_tags (
  `id` int(11) NOT NULL auto_increment,
  `tag` varchar(20) NOT NULL default '',
  `date` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  AUTO_INCREMENT=310 /*!40101 DEFAULT CHARACTER SET " . COLLATE . " COLLATE " . COLLATE . "_general_ci */";

Code (markup):

I sure hope one of you smart guys/gals can help me with this. Thanks in advance!

 

Could you give some more information regarding this system.
At the moment all I can see is an drop on table tag and a create on table tagS which is odd.
What is the PREFIX used for ?

And I assume the table is a lookup table used for searches but then it would not be filling up with multiple searches.

Give some more insight into the design and please include the queries themselves and where they are used.

 

I don't know if knowing the site would help, but it's http://www.mp3chimp.com, it's the search tags on the right. I really don't know anything about Mysql, so I wouldn't know where to begin to resolve this issue. Once I start getting too many rows, when the search cloud is called upon, it sometimes takes so long to go through the table, I get a cpu overload message from my server.

 

So you have a system that feeds new records into this table so that when I open singerX it will add a record singerX into this table the date column is used to only look at current rows or remove old rows.
The cloud Query is something along the lines of select tag, count(tag) from _tags group by tag maybe with an optional cutoff date.

First question since the table fills up do you have a reaper script that removes old data if you only measure X days, this would be a delete statement that does someting with where date < (current date - interval xdays).
That is assuming that the cloudinformation is only measured over X days and not since inception.
So you either have an reaper script that purges old information or your select / group by statement does something with where date > (current date - interval xdays) possibly with an index on the date column.

If you are not capable of changing the database schema(design) and updating the code it might be best to schedule an hourly / daily script that deletes all records older then X days. If you can't schedule a script to do this there is a less elegant approach by finding all statements that add rows into this table and place the delete older X days before or after that statement, in effect cleaning up every time a modification is made to the database. Less efficient but gets the job done.

If scalability due to the GROUP BY becomes an issue after that you could ask someone to enhance your current script so it won't add a row per view but only add a row per tag. The table needs to be extended with a viewcount(Integer) column that records the total views for this tag on that date in your site.

 

Thanks for your help. I do not have a reaper script, I've never head of one. Like I said, I have no real knowledge about mysql, so I don't know what to do. What would be the easiest thing for me to do? Is there a way I could reinstall the table, and add something to it to auto delete after x amount of rows, or x amount of days?

 

No you can't do it from within Mysql itself, it does not have an internal scheduling system, you need to schedule a task/program/script with crontab(Unix) TaskScheduler(Windows) or use the delete after insert technique within your php scripts.

If you are familiar with php and queries in general I would say the easiest way for you is to place a delete older X days after the insert a row in the tag table statements. This would be a nice stopgap until you find someone to review your current cloud scripts. If you can't do this yourself you might need to find somebody here to do it for you it is not hard, but requires some basic understanding of Mysql and SQL.

Judging by your followup information I suspect that outsourcing might be best since your skills are in other fields of the Web judging by the mentioned site