mySQL Field Indexing

[microbluechip]

Hi all,

I have a database which currently contains only a few records in each of the tables. However I have also set up indexes for the main fields which are used in the WHERE clauses of my SQL statements.

My query is that when indexing is enabled on these fields the SQL is relatively slow at returning the result compared to running the same SQL with the indexes removed.

I am presuming that this is because it is quicker to sequentially search the tables rather than use an index while there are relatively few records within the table initially.

Can anyone pass on their views on this and whether I should keep the indexes or not.

MicroBlueChip.

[ajmoore]

Indexes are a good idea when used properly. I recently redeveloped a help desk system that was in use at the company I work for...The database stores 200k+ records and the original version was not indexed at all. Queries took a good 30-45 seconds to operate using that database. After restructuring the database and adding some indexes, most queries finish in 1-2 seconds. If you're expecting the database to grow beyond just a few records I would definitely keep the indexes there....