Improving MySQL Performance

[dartiss]

Hi,

Are there any coding techniques that are recommended for improving the reading time of small MySQL tables?

My site reads a few for page generation and, although the site is hardly slow, it's one area that could be improved.

Either SQL coding or the way my tables are defined.

If they are small, will having indexes be slowing them down? If so, should I have a unique field in preference to an index, for instance?

All help appreciated.
David.

[chazzy]

indexes should be improving your performance. it's one of their useful features.

MySQL has a feature (which is generally everywhere) called EXPLAIN. You give it a select and it tells you what it's doing.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

[dartiss]

Hi yes. Given that a go and my uses are quite basic, so it doesn't really raise anything.

I didn't know if there were any general tips on performance that might be of use. For example, for small tables, should I or shouldn't I be indexing them?

David.

[chazzy]

you should be indexing as much as possible.

[Nedals]

Without seeing your SQL structure, it's difficult to tell what can be improved.

Mulitple calls to the database will slow things down. It's better to use joins with a single call.

As to Indexing:

With small tables it won't make much difference.
If you do index, it's important to index columns that will appear in your 'where' clause.

The more columns you index will typically improve SELECTs but will slow down UPDATEs and INSERTs

[dartiss]

Thanks to everyone who replied.

I found the cause of the performance issue in the end... it was a bit of a school-boy error. Never-the-less I've improved it even further with some new indexing and some other tweaks here-and-there.

David.