Using indexes to improve performance can be a challenging tasks! But it can give a serious boost to your search pages as well…
The question when to use which indexes cannot be answered easily. It depends on many factors, like:
- System resources
- Number of rows in a table
- Table structure
- Number of concurrent users
- And many more…
It are often not those factors on its own, but the combination of them that makes indexing such a tough and interesting! issue. Here are some considerations that might help. Keep in mind that every situation is different and needs an analysis on its own.
Normal exact search
With normal exact search, indexes can speed up your performance. But don’t forget that every index has to be maintained as well. Adding an index to every column to improve search performace, might lead to slower inserts, deletes and updates. Ask yourself: is this table mainly queried or mainly edited? Add only indexes to columns when you need them.
Normal wildcard search
It makes no sense to create indexes with normal wildcard search. They will not be used.
Full-text search improves performance for large text columns and large tables having text columns for which wildcard searching is required. Be aware that full-text index tables have to be maintained as well. Every insert, delete and update forces your database management system to update your full-text index tables. Full-text indexes are probably more efficient if you mainly query a table. If you mainly edit a table, a full-text index might slow down your server.
If your tables are small and you have sufficient memory, you might prefer to not use any indexes at all. If your result set can be cached completely in memory, subsequent queries will be fast even without indexes.
But what is a small table? And what is sufficient memory?
Remember I said “it is the combination of factors that makes indexing such a tough and interesting topic”? If you have 1000 page views per day and mostly no concurrent users, just forget about indexes. If you have 1000 page per minute and 100 concurrent users, you’ll need indexes to keep your website running! In other words: analyse.
Analyse your situation before you start to create indexes. If wildcards searching is required, you’ll probaly need indexes. But keep in mind that your indexes are not used with normal wildcard searching. If normal exact searching is not an option, full-text search might help you out.
If possible, perform your analysis on your production server. It makes no sense to perform an analysis on a test machine with no concurrent users and all resources just for you alone.
Perform your analysis on a server with the same characteristics if you cannot perform your analysis on your production server. Use tools to generated requests to stress test.
Most important: analyse how users perform searches! It makes no sense to enter a query just to see if you’re getting results. Your developer queries are usually too optimistic. Real users have different needs. They dont think about the technical consequences. Talk to your users! Let them be your teachers…