indexing - MySQL indexes - what are the best practices? -

I've been using indexes on my MySQL database for a while, but never learned about them Generally I have put an index on any of the areas that I can search or select by using the WHERE section, but sometimes it does not look like it is black and white.

What is the example / dilemmas:

If there are six columns in a table and all of them are searchable, then I want all the indices Or any of them?

What are the negative performance effects of indexing?

If I have a VARCHAR 2500 column that is being searched from parts of my site, should I index it?

You should spend some time reading indexing, a lot about it written Is important, and it is important to understand what is happening.

, and the index places an order on the rows of the table.

For simplicity, imagineing a table is just a large CSV file. When a line is inserted, it is inserted at the end , so the "natural" order of the table is in the order in which the rows were inserted.

Imagine that you have loaded the CSV file in very basic spreadsheet applications. It displays all spreadsheet data and shows the number of rows in sequential order.

Now imagine that you need to find all the rows that have some value in the third column "M". Given that what you have available, you have only one option. You scan the table by checking the value of the third column for each row, if you are getting multiple rows, then this method (a "table scan ") Can take a long time!

Now imagine that in addition to this table, you have got an index. This special index is the index of values ​​in the third column. The index lists all the values ​​from the third column, in some meaningful sequences (e.g., alphabetically) and each row provides a list of numbers where this value is displayed.

Now you have a good strategy to find all the rows where the value of the third column is "M" for example, you can do one! While you need to see N lines for table scans (where N is the number of rows), binary search only requires that you see log-N index entries in very bad condition. Wow, it's easy to make sure!

Of course, if you have this index, and you are adding rows to the table (in the end, since this is our ideological table), you update the index every time, You work a bit more while writing, but when you are searching for something you can save a ton of time.

So, in general, the index creates a control between the readable efficiency and writing efficiency, with no index, the inserts can be very fast - the database engine just adds a line to the table when you If the index is added, the engine will need to update each indicator during inserting.

On the other hand, it gets very fast.

Hopefully this covers your first two questions (as other people have answered - you have to find the right balance).

Your third scenario is a bit more complex. If you prefer, then the indexing engine will usually help you speed up your studies by "%" first. In other words, if you start 'column' with 'FU', then it is necessary to scan the intermediate rooft to find the subset, where the index will be used to find all the rows. Select the "bar" that is included ... where the column can not use the '% bar%' index, I hope you can see why.

Finally, you have to start thinking about indexed more than one column. The concept is identical, and behaves like the same material - essentially, if you have an index on (a, b, c), then the engine will continue to use index as left to right, as best as possible. is. Therefore, a search on the column (A, B, C) can be used on the index, such that (A, B) will be one, however, the engine will need to scan the full table if you want WHERE b = 5 and c = 1 )

Hopefully this will help a little bit of light, but I must be repeated that you spend a few hours in digging around for the best articles explaining these things with the best depth. Do your special database head It is also a good idea to read the documentation of the document. The way the indexes are implemented and used by the query planner, they can vary widely.