All table columns should be indexed or not mysql database?

Question

I want to make one table with 4 columns. The first is the primary key, and the remaining three columns are links to columns of other tables. I want to use it to combine these four tables to create a search filter. These compounds take time.

I thought I should index these columns because I read that adding indexes to the column used in join conditions [makes them work faster]. My question is, will there be a problem if all the columns of the table are indexed? Or is there another way to reduce the time complexity of the search filter. Thanks in advance.

More Hint: Table 1 (main search) -1000 records main key fk1 fk2 fk3

Table 2-800 records pk1 .. (8-9 columns)

Table 3-700 records pk2 .. (10-12 columns)

Table 2-850 records pk3 .. (7-8 columns)

Answer 1

Creating an index requires additional disk space, and too many indexes can cause problems due to file system size limitations, so you need to use thoughtful thoughts to select the correct fields for the index.

Since indexes are used only to speed up the search for a suitable field in records, it is clear that indexing fields used only for output will simply be a loss of disk space and processing time when performing an insert or delete operation, and therefore this should be avoided. Also, given the nature of the binary search, the power or uniqueness of the data is important. Indexing in a field with a capacity of 2 would split the data in half, while a capacity of 1000 would return approximately 1000 records. With such low power, efficiency comes down to linear sorting, and the query optimizer avoids using an index if the power exceeds 30% of the record number, which makes this index a waste of space.

This is the best way to add indexing to a group of columns.

Answer 2

Indexes are not magic tablets.

Of course, they can speed up the requests, but they also slow down the recording (inserts / updates / deletes) and take precious RAM.

Use them carefully.

Answer 3

The columns of the link table must have an index in which the columns of the foreign key are listed as the first columns in the same order.

Such an index is automatically created in the link table if it does not exist.

Go through the articles below to understand the dough: How to specify for joins with MySQL

Answer 4

To properly index your data for performance, you need to understand your data. Hypothetically, let's say I created a census database table:

CREATE TABLE CENSUS ( ID INTEGER NOT NULL, GENDER CHAR(1) NOT NULL, FAVOURITEFOOD NVARCHAR(20) NOT NULL, STATE NVARCHAR(20) NOT NULL ); 

Due to working with data, I can know that:

• Sex is divided into 50% of men, 50% of women.
• 60% love roast, 20% vegetarian, 20% Asian food, 20% pasta.
• The state is 25% Californian, 25% in New York, and the rest is 1% for each state.

If I wanted to find Masculine people who love fries and live in California, I would think about making a multi-column index, becoming the first STATE (STATE, GENDER, FAVORITY). I make FAVOURITEFOOD the last column in the index. This is because the STATE filter will clip data by 25%, while FAVOURITEFOOD will return most of the database (no better than a full table scan).

If I wanted to find Feminine people who love vegetarian food and live in New York, I would think about creating a multi-column index and starting FAVOURITEFOOD (FAVOURITEFOOD, STATE, GENDER). Here FAVOURITEFOOD beats data by 20%, so it selects the other two columns much better.

If I run BOTH queries often, what index should I do? The answer is:

 CREATE INDEX IX_CENSUS_001 ON CENSUS (STATE, GENDER, FAVOURITEFOOD); CREATE INDEX IX_CENSUS_002 ON CENSUS (FAVOURITEFOOD, STATE, GENDER); ANALYZE TABLE CENSUS; 

The ANALYZE TABLE command stores the key distribution for a table. Now, when you run any query, it will determine if IX_CENSUS_001 or IX_CENSUS_002 is the best metric for the execution plan.

If I want to start executing various types of queries, I will stop and think about my data again. I may need to add a new index, and I may need to run ANALYZE TABLE again.

So, back to your scenario; it depends on the data that you have in your tables, and on the queries that you want to execute on it.

Answer 5

MySQL allows several types of indexes, such as a primary key index, a unique index, a normal index, also known as ("not a unique index", a regular index, an unrestricted index ") and a full-text index. Of course, indexes improve SELECT very quickly, but There are also significant drawbacks: The benefits of MySQL indexes

Generally speaking, indexing MySQL into a database gives you three advantages:

 Query optimization: Indexes make search queries much faster. Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data. Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such. 

check this

Answer 6

Adding an index to a column means that the database needs to do more work on each record, but can save time on some reads.

If your queries are time consuming, adding a coverage index for joins can help speed up the process, but as with any optimization, make sure you have the right metrics to compare with before and after "optimization."

However, since you are joining the table per column, you do not need indexes, since you already have the primary key, and in any case, you need to read the full row.

Answer 7

I think that indexing all four columns will not really improve your performance, because you will still need a full index scan, which will essentially be the same as a full table scan. Your index will be just a repetition of the data in your table. Can you insert your request?

Answer 8

Instead of adding indexes, if you add the primary key to the main table to other tables, and joining tables in this field will be faster.