Database Indexes

[Anon]

Hi everyone 🙂

Okay... I've got a nice database setup. To speed up the pages in my site that use the DB I'm going to use some indexes.

The DB gets new data all the time, so the indexes have to be continuously recreated. To do this, I'll use a Cron Task to run a PHP script every so often. The PHP script will simply take a new DB dump and make a new index. I was planning on setting the Cron to run every night at midnight.

My questions are:

1) First of all, does all of the above sound OK? Is there a better method?

2) How long will the pages on my site be out of action when the Cron is running. (Assume I've got 5 tables, each with around 300 records, and the index uses all of them. Imagine a big meaty WHERE clause in SQL. Oh and yes, my Tables have been Normalised, so don't worry about my DB design!)

2) What would actually happen if you tried to use the pages when the Cron is running. Will you get a big nasty DB error? Or will the page automatically use the orginal DB tables, because it can't find an index (slower, but better than an error)?

3) I'd love to recreate the indexes more often than once per day. Once per hour would be much better. Obviously this is only possible if the Cron runs really fast. What do you think?

Thanks in advance for all your help ,

Ryan.

[[deleted]]

What kind of indexes did you have in mind?

'Normal' database indexes are maintained by the database itself and do not require you to do anything.

So:

1. The above makes no sense for normal indexes

2. a few milliseconds

2a. cannot answer untill you explain what you mean by indexes.

3. again, cannot answer until you explain your plans a bit more.

[Anon]

Hello,

if you talk about setting index to the property of a field, you have to note that:

• index field will manage itself fine, no need to recreate the index

• The primary key is always automatically an index field.

• make index field on fields that you will query often, if not it will slow things down.
  A good idea is to index your field that really need it after the database is active for a few months, that way you know wich field to index.

py