Menu
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.
843
No, you should not index all of your columns, and there's several reasons for this: There is a cost to maintain each index during an insert, update or delete statement, that will cause each of those transactions to take longer. It will increase the storage required since each index takes up space on disk.
MySQL's documentation is pretty clear on this (in summary use indices on columns you will use in WHERE , JOIN , and aggregation functions). Therefore there is nothing inherently wrong with creating an index on all columns in a table, even if it is 60 columns.
There is one more reason besides space and write performance: using multiple indexes for a single table access is very inefficient. That means, even if you have one index on each column, select performance is not very good if multiple columns are accessed in the WHERE clause. In that case, a multi-column index is best.
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.
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
136
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.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
