Index database daily

[brattt]

I have an sql database serving a website which is accessed by hunderds of people every day, and this is causing slow performance. I have been advised to generate an index every night but have no idea how to do this. Any advice would be greatly appreciated.

[bpat1434]

CREATE INDEX

There's the MySQL documentation on it. Do a quick read over that. You really should have created an index from the databases inception, rather than waiting until now. But it shouldn't be too difficult.

Although indexing your database would help, I feel that it's not the only issue. Have you looked at actually caching some of the pages? That way, when a user browses to your site, they see a cached version, rather than a dynamic one? It wouldn't be that difficult to create a simple cache system. Something like you have a table in a DB that lists the page, and the timestamp of the update. You compare that with the current time. If the current time is say less than 6 hours old, you show the cached version; otherwise, you generate a new page and store that in the cache and update the cache table.

That would really lessen the burden on your RDBMS and speed up serving of pages.

[brattt]

Thanks for the reply. As the database is constantly updated, caching the pages won't really do me any good. I'll see what I can come up with as far as indexing goes.

[MarkR]

Creating an index on a table is something which only needs to be done once. Thereafter, it will automatically maintain the index.

In order to know why your application is slow you must run benchmarks on its queries. Adding indexes willy-nilly won't help, they must be placed on the right columns in the right tables.

You may be using some queries for which indexes won't help at all, in which case you need a design change.

We really can't help unless you are far more specific.

Mark