Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

All columns of table should be indexed or not mysql database?

I want to make one table with 4 columns. One is the primary key and the other three columns are references to columns of other tables. I want to use it to join these four tables to make a search filter. These joins are taking time.

I was thinking I should index these columns because I read that adding indexes on column used in join conditions [makes them run faster]. My question is will there be a problem if all columns of the table are indexed? Or is there any other way to decrease time complexity of the search filter. Thanks in advance.

More Hint: Table1(main search)-1000 entries primary_key fk1 fk2 fk3

Table2-800 entries pk1 ..(8-9 columns)

Table3-700 entries pk2 ..(10-12 columns)

Table2-850 entries pk3 ..(7-8 columns)

like image 840
Somnath Muluk Avatar asked Jan 20 '12 10:01

Somnath Muluk


People also ask

Should I index all columns MySQL?

No, you should not index all columns. It will be slower when you write data.

Which columns should be indexed in MySQL?

column the first in the composite index (in my case that would be the Showtime column). The only problem with that is that the index can only be used by the database if the first column is included in the search query, which it currently isn't in either of my queries.

Why we shouldn't index every column in the SQL table?

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.

Can I CREATE INDEX on all columns in MySQL?

In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns. The indexes are formed by concatenating the values of the given columns.


1 Answers

Creating an index requires additional disk space, and that too many indexes can cause issues arising from the file systems size limits, careful thought must be used to select the correct fields to index.

Since indexes are only used to speed up the searching for a matching field within the records, it stands to reason that indexing fields used only for output would be simply a waste of disk space and processing time when doing an insert or delete operation, and thus should be avoided. Also given the nature of a binary search, the cardinality or uniqueness of the data is important. Indexing on a field with a cardinality of 2 would split the data in half, whereas a cardinality of 1,000 would return approximately 1,000 records. With such a low cardinality the effectiveness is reduced to a linear sort, and the query optimizer will avoid using the index if the cardinality is greater than 30% of the record number, effectively making the index a waste of space.

So better to add indexing on group of columns.

like image 51
Avinash Avatar answered Nov 11 '22 08:11

Avinash