Okay, I just recently got exposed to Indexes and still have yet to fully grasp how to use them.

Say I give a table a KEY (Index) on a column, when I'm using any select statement to draw from that table and if I use a WHERE condition based on that column, does it use the Index by default? Or do I have add something else in the SQL statement.

Yes, your database engine should utilize the index for the WHERE clause when possible.

Note that different database servers do have their own little quirks as to how they use indexes, how many they can use, whether they can order by them, etc. So consult your database vendor's documentation if you're doing complex performance-dependent queries (joins, multiple column WHERE clauses, multiple column indexes, fulltext indexing, ordering, etc).

However, for the basic example you provided (ie. SELECT field FROM table WHERE column = value), if column is indexed, then your database should use the index to filter results.

Great answer bilalk.

I'd simply like to add that you can get the database to tell you what its using for queries - most rbdms have a facility to do so. For MySQL the statement is EXPLAIN.

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

Ahh, I figured. Thanks a lot guys! I've been so focused on optimization lately, which is why I had to ask just to make sure. I use MyISAM, but I might switch to Innodb later, as I've heard its much faster in certain areas of fetching data.

---

One more question, I was talking to a fellow programmer and he said it was bad for a table to have too many columns, so I was thinking.. shouldn't it be okay to have a lot of columns as long fetch from the table correctly?

For example, the following should work efficiently without slowdown..

The table is for holding user data, there is about 100,000 rows.
The table itself has upwards of 90 columns in it, almost half are TINYINT and INT.
I have a PRIMARY KEY on the user's id column, and its a unique column.

Good statements?..
- SELECT * FROM users WHERE id='$id' LIMIT 1
- SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9 FROM users WHERE id='$id' LIMIT 1
- SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9 FROM users LIMIT 20

Bad statement..
- SELECT * FROM users LIMIT 20

There are no hard and fast rules about good/bad table design in terms of how many columns the table has. To be able to comment we'd need to have some understanding of what the data is and what problems you are solving. If the best way to represent the information is a table with 90 columns, so be it. Whether its efficient or not for the database depends much on the database itself and its internals - that's something that one should try to forget about as much as possible.

As far as the statements:

- SELECT * FROM users WHERE id='$id' LIMIT 1

If id is a primary key column, then LIMIT is redundant. There will only be one key with the same value in that table.

SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9 FROM users WHERE id='$id' LIMIT 1

Ditto. Selecting only the columns you need and not * is good practice.

SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9 FROM users LIMIT 20

What you want is the 20 results and you don't care much which results (there being no ORDER BY on this query, many databases will return whatever results they find most recently - perhaps in a cache - rather than the first 20 records in the table.

Bad statement..
- SELECT * FROM users LIMIT 20

Ditto per last comment above. However whether this is "bad" or not depends on what you need. If you are using much of that data (say displaying in a grid) then there is nothing wrong with this, provided that's indeed what you want.

Ah, that explains a lot. It also makes sense to not use the LIMIT 1 on a unique index, just a bad habit of mine.

So having an index on a column that only has values like 1-5, will actually help?

What the index does is prevent the database from having to scan the entire table to get your result. So yes, if the table is large, it will make a difference.

I wonder if your 90 columns of data in one table is optimal. If its possible to describe what the data is, perhaps a different approach might become obvious.

I wish I could link you an excellent resource in BCNF (Boyce-codd normal form), but the only good information I have are my old college handouts.

If you can find a good book it'll help A LOT. BCNF guarantees your database is optimized/normalized and even tells you which fields should be your Super keys (indexes) and keys.