> down the result set would make things faster..? Wouldn't the selecthere:
CREATE TABLE tmp ( flag boolean, name text );
SELECT name FROM tmp WHERE flag = 1 AND name LIKE '%foo%';
run faster with an index on the flag column since it can scan just the flag = 1 rows instead of the full table?
I think this is one of those big-O things, right? It might be faster, but only a bit faster, and not enough to justify the hassle of creating and maintaining the index. --Keith
A lot of optimizing is context specific. Certain kinds of optimizations work well on some sets of data, but can make things worse with others. The index being a type of optimization. On some data sets, indexes can make things a little faster, and on others, orders of magnitude faster; the latter in particular is where you want to target them.
The main time indexes are useful is when you only want to return a tiny fraction of the records in a table, in a typical select. For example, use an index when you want to fetch, say, 1 row out of a table with 1000. But if you want to fetch 200 rows out of the same table, then not having an index will be faster for fetches. And you save time by not creating the index too.
When you use an index, the overhead of storing rows is definately greater, and the overhead of fetching is also greater. However, that overhead is only on the rows actually returned for a fetch, so you still have a huge savings with an index if you return just a few rows. But if you are selecting a lot of rows, such as the 200, then the overhead of finding them first in the index leads to more work being done than if the index was ignored and the same 200 were found with a simple table scan.
One of the best places to have indexes is usually on columns where every value is unique, regardless of how many records you have in the table. But then, if you have a unique or primary key constraint on the column, then an index is created implicitely anyway, as it is used when enforcing the constraint.
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
