My first question is,

I want to be able to delete rows in a MySQL database after a certain amount of time has passed. For example, lets say the time limit for something to be deleted is 30 minutes, I insert a row into the database and the time is 12:00, and at 12:30 the row is deleted by the database automatically. Is this possible and if so how would i do it?

My second question is about speed on a database. How do you know how fast your database is and how would you make it faster?



Thanks in advance!

Ok, on your first question: Deleting after a certain time has passed.

There are a number of ways of doing this. Probably the easiest from a conceptual point of view is to add in a time field and then execute an appropriate command to delete based on the value of the field.


Now, as for database speed.

A database is a collection of data. It does not necessarily have "speed" as it's an object, per se. Speed comes into play when you attempt to ask the database a question via a query. Subsequently, your question ought to be "How do I determine how fast a query is?" The answer for MySQL is to use EXPLAIN. What I found interesting about EXPLAIN is that when I first learned to use it, I'd try EXPLAIN with a small set of data. When I read the "MySQL Database Design and Tuning" book, however, I noticed that they first inserted lot's of rows (in the millions) before executing EXPLAIN. The rows inserted, while being randomly generated, had the same characteristics that real data inserted into the database would have in order to make the results of the EXPLAIN more relevant (oftentimes providing drastically different results than the EXPLAIN on a smaller set of data). Now, the results of EXPLAIN have to be interpreted carefully and I recommend checking out the MySQL site or getting their book for greater insight. Additionally, it's very interesting to note that the latest version of MySQL is much more efficient at executing the same query than previous versions of MySQL (for example, through more efficient use of indices), so upgrading MySQL may decrease your query's execution time.

I am not sure if you are taking the right approach, I have never seen such a requirement in last 15 years of my programming experience.

Anyway, a requirement is a requirement.

If I were you and I had to delete rows from the database after 30 minutes and I did not have access to a CRON function which can execute a PHP script to clear up stuff every 30 minutes, then I will use the following method.

I'll create a trigger on the table for all operations ON SELECT, ON UPDATE, ON INSERT and compare the timestamp stored in the table rows with the current timestamp and then if the difference is > 1800(60 * 30), delete row.

--Amit.

Database speed:

Database speed can be improved drastically using Indexing in MySQL.

Without going deep into the issue, here is a quick glance at how to use indexes to improve database performance.

If the number of rows in a table is > let's say 5k, the indexing the table could improve speed.

Here's how..

Consider a table books with the following fields

BookID
BookName
ISBN
Description
Author
Category
Type(Paperback,Ebook)
Price
Discount

In the above table, the query "select BookId, BookName from Books where Type='PaperBack' and Category='Fiction'; " will run faster if you have an index
on Books containing "BookId,BookName,Category,Type".(4 columns only)

Bottomline, "create separate indexes for queries that are time consuming and include only those columns that are required."

Similarly, FullText Indexes can be used to perform faster search functions as compared to %LIKE% based searches.

The performance difference will be HUGE if the number of rows are more say 1 million rows.

NOTE: Indexing will require more diskspace.

--Amit

How fast your database is is too broad a question to answer in a forum like this, without a hell of a lot more information.

Run a trace See how people are using your database, what's being run most often, and what's taking the most time or resources to execute. You could have a query that takes a frustrating several minutes to execute that runs once a week, and another query that takes ten seconds but runs several times a minute. Which do you suppose you'd do better to improve?

Indexes are a good way to improve data access time, but they slow data modification and creation. So while they're the #1 answer, they take some care to use properly, or you can mess things up.

The slowest thing your db server does by routine is I/O against the storage subsystem. ( Network I/O is slower, but far less bytes are sent over the wire. ) Adding more drives, ideally RAID, and balancing the I/O for max concurrency is going to have a tremendous effect on all database functions, except what's already in the data cache.

But you really can't make your database faster until you know what it needs to be faster at.