I have a couple of questions regarding MySQL indexing:
1) Is there any speed increase when indexing a table stored in memory?
2) When searching my table I match on column field, would indexing every column defeat the purpose of an index?
Many thanks.
Indexing any table, either memory or file system based, will speed up queries that select or sort results based on that column. This is because the index works like a tree structure and the search distance depends on the depth of the tree, which increases a lot slower than the row count of the column (logarithmic).
Indexing every column does not defeat the purpose of the index, but it will slow up inserts and updates because those changes will cause an update of every index of that table. Also, the indexes take up space on the database server, so that is another drawback to be considered.
Other SO questions to read relating to this question:
Best practices for indexing
What is an index
How many indexes are enough
1) Yes, of course.
2) No, it doesn't defeat the purpose of index. Just remember that mysql can't use more than 1 index per table and that adding more indexes slowdowns insert/update/delete operations. So avoid creating indexes that aren't used, create multicolumn indexes that matches your queries best instead.
Re Q1... The query Optimizer sometimes chooses to scan the table even when there is a "perfectly good" index. This tradeoff is based on a complex algorithm, but, as a Rule of Thumb:
If more than ~20% of the index needs to be used, it is deemed more efficient to ignore the index and simply scan the table.
The reasoning for this is: Using an index means scanning the index BTree (which looks very much like a table), then jumping over to the data BTree to find the record. This back-and-forth is avoided if it simply scans the data. The drawback is that it needs to ignore up to 80% of the rows.
Corollary: Don't bother indexing "flags" (0/1, T/F, M/F, Yes/No) or low cardinality columns (yes/no/maybe, M/F/etc, day-of-week, ...).
On the other hand, it may be very useful to have a composite index starting with a low cardinality column:
WHERE deleted=0 AND created_at > NOW() - INTERVAL 1 DAY
INDEX(deleted, created_at)
The cost of an index in disk space is generally trivial. The cost of additional writes to update the index when the table changes is often moderate. The cost in additional locking can be severe.
It depends on the read vs write ratio on the table, and on how often the index is actually used to speed up a query.
Indexes use up disc space to store, and take time to create and maintain. Unused ones don't give any benefit. If there are lots of candidate indexes for a query, the query may be slowed down by having the server choose the "wrong" one for the query.
Use those factors to decide whether you need an index.
It is usually possible to create indexes which will NEVER be used - for example, and index on a (not null) field with only two possible values, is almost certainly going to be useless.
You need to explain your own application's queries to make sure that the frequently-performed ones are using sensible indexes if possible, and create no more indexes than required to do that.
You can get more by following this links: For mysql: http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-are-advantages-and-disadvantages-of-indexes-in-MySQL
