What does Optimizing do?

[jake66]

Stupid question, asked many times before I'm sure (I searched through google prior to asking this question - only found brief explanations really)

I was going through my database tables tonight, checking to make sure my script was deleting things properly. I noticed a yellow background with red text reading "Overhead" and "47,560" with an "Optimize Table" link beneath.

When I clicked the Optimize table link, that red text went away.

In dummy terms: Specifically, what does the Optimize Table do? I've read it "speeds things up", but doesn't deleting obsolete records do this?

Also: Indexes.
I also read these "speed things up" but to where should I assign the index? The most commonly searched table or on the id? (How do "indexes" work?) Can I have more than one?

[Matt Probert]

The term 'database' is generic. It refers to any collection of data. Any particular RDBMS (relational database management system), often colloquially, confusingly and erroneously called a database, will have different ways of deleting records. These may result in unused disk space still being occupied, in which case optimisation may reclaim the disk space, and "push all the records togther" so as to make data access faster - not that you'll notice on many new PCs.

An 'index' is just the same as an index in a book. It provides a sorted (perhaps physically, perhaps with a linked list) cross reference to data, and as such can be searched for a particular piece of data quickly. Obviously one then creates indices (you can have as many as you like) for fields which are frequently searched for, eg 'name' or 'product code'.

How an index is accessed will depend upon the RDBMS in use, popularly a binary search will be employed, testing the middle entry of the index and narrowing down the search accordingly by bisecting the part of the index to be considered until a match is found, or absence of a search term is established.

Matt

[aspdaddy]

for both these you need to read up on physical database design for your RDBMS. Its a complex subject.

Good database design has three seperate stages, conceptual where you identify the entities, atributes and relationships, logical where you aim to normalise the design and then physical when you decide how to implement the design to the target softwares features.

The last stage is where indexes and optimisation come up and its the hardest part to get right.

[jake66]

What is "RDBMS"?

I use my Mysql database to store information from my php scripts and such, so all of this stuff I should read up on and be familiar with to strengthen it's performance?