From what I understand, indexes make SELECT queries faster. Questions: 1) I've gone ahead and made every single column to be used in the WHERE clauses of SELECT queries an index. Was that the correct thing to do? 2) If 2 columns were going to be used in the WHERE clause and one of them was already an index, must I still make the other one an index? For example: SELECT * FROM table_name WHERE column_a=X AND column_b=Y; Code (markup): If column_a is an index, do I still need to make column_b an index?
It depends on the situation. If your query is still slow, then I would try placing an index on the other column. If not, then it's fine not to have it. If you're always using this query structure, you can place a single index on both columns. CREATE INDEX combined_index ON table_name (column_a, column_b); This will be beneficial when querying with both columns in the WHERE clause. It is larger than a single index, bu t usually is more efficient than using 2 seperate indexed.
I second that jestep An index can make the where clause faster, so the SELECT, UPDATE and DELETE statements that use a where clause can be faster if an usable index is available. Sometimes joins can be sped up as well. BUT - Sometimes an index can't be used - The queryplanner thinks alternative strategies are faster then the index - Changes made by INSERT, DELETE and UPDATE statements needs to be updated in the index as well Create an index for everything you need to get acceptable speeds, but don't create more indexes then absolutely necessary. 1) That depends on the database server used and the where clauses that you use that you need to speed up Every database server has specific conditions governing the usability of indexes. You have not stated your database server so find the manual of your database server and read the index section, where can it be used, where can't it be used. A better strategy would be to drop all your indexes and use your program, then when you find an unacceptable level of performance, that can't be enhanced by a better algorythm only then create an index to try to increase the speed of the specific bottleneck. 2) Ussually an composite indexa-b on column(a,b) is the fastest, but if you do where clauses containing only the b column then you might want to create an indexa and indexb IF your database server can perform index merging. Otherwise you might end up creating to many / unneccesary indexes that need to be update for every insert/delete/update. If your application is mostly selects the you might not care about the unnecessary indexes.
You know when you post a simple, unambiguous question in a forum, and someone gives an irritating answer like "It depends..." Well, I'm afraid it's just about to happen again... Indexes make SELECTs faster, but UPDATEs slower. Whether to make every WHERE column indexed will depend on your overall system performance, and the ratio of SELECTs to UPDATEs. To be fair, most databases used with web applications tend to have many more SELECTs than UPDATEs. For instance, a database being used to power a highly-trafficed blog might have, at most, half a dozen updates a day, but hopefully many thousands of selects. In this case, putting an index on every single column mentioned in the WHERE clause is probably your best bet. You don't have to, but you don't get the full benefit of the indexes unless you do. The only situation in which I might not bother is if the first condition already reduces the number of rows selected to a fairly small number. But, as above, with most web databases, the number of SELECTs typically vastly outweighs the number of UPDATES, so indexing them both is probably worth it.
But you (the threadposter) have one advantage when the answer contains an "It depends..." because then you get information and you must decided how to use this new knowledge to learn and enhance your system and ultimately yourself. If we had known all the variables / queries database schema, hit ratios in your system, then we could have given a more exact answer. But in turn it would be harder for you to understand how we came to that answer. Cheers everybody
We're currently using MySQL for our enterprise storage and our primary data reporting system (something like 300 million records growing at 50M per week). We've played with indexes a lot on it to try to keep the speed reasonable. With our indexes and configuration optimized to the best benchmark that we can reliably get, we can get normal queries down to a few milliseconds. If we remove even a single index in a select statement, a query could take well over 30 seconds. Just an example of a make or break system. The best option is to research and test, and repeat. There is truly no one-size-fits all configuration for a database because there the way it is used, the size, and the structure make configuring one different in every situation. The real frustration comes when your database is so big an alter table command takes minutes to run... Testing gets tougher with bigger data-sets.
Some really great responses, guys! I don't know the technical specifications of my database. It's on my HostGator account - that's all I can tell you. The database consists of 7 tables: 5 parents 1 child 1 parent/child There is a lot of updating going on, with some columns being updated per click. There are different SELECT queries on almost every page, using different joins, and different columns in the WHERE clauses. Basically, it's quite a complicated schema - which is why I didn't attempt to describe it. I think I might take Chisara's advice and drop all the indexes, until I see a necessity for their addition. Thanks for the help, guys! I've added reputation points to your accounts. (For some of you, the system didn't let me add points because I have done so recently in other threads.)