There are many requests to the database of the following form:

SELECT `id` FROM `table` WHERE `ip` = '000.000.000.000' ORDER BY `updated_at` DESC

Question: Will the ip and updated_at columns be correctly indexed together or only the ip column? What type of indexes should I use?

In a table> 6 million rows, will indexing slow down if I make a request to create an index right now?

    1 answer 1

    Indexing, on the contrary, improves optimization. In this case, you need to make a composite ip index and updated_at CREATE INDEX ip_updated ON users(ip, updated_at);

    Your case is described here.

    Sorting

    Composite indexes can also be used if sorting is performed:

    SELECT * FROM users WHERE gender = 'male' ORDER BY age In this case, we will need to create an index in a different order, because sorting (ORDER) occurs after filtering (WHERE):

    CREATE INDEX gender_age ON users (gender, age); This order of columns in the index will allow you to filter by the first part of the index, and then sort the result by the second.

    • Thank you, I mean, if I’m going to slow down the work, I’m going to fulfill the request, and new lines are being actively written there, everything will be ok?) - Vladimir Solovyov
    • 2
      @ Vladimir Solovyov Any index slightly slows down the insertion of new data, as it may be necessary to restructure a part of the index. But if you do not dozens of different indexes on the table, the difference will not be particularly noticeable. Although if you insert a million lines every day, then any additional index is quite serious - Mike
    • @ VladimirSoloviev In theory, they will fit in, in any case, you can use EXPLAIN and other queries to evaluate the "effectiveness" of the indices. Optimization and indexing is a regular thing, not “made and forgotten” - Peresada
    • @Peresada Thank you. Indexed. 1.5 minutes DB didn’t answer. I got scared) let's see how it will be in the end - Vladimir Solovyov
    • And if you also change the field type for IP from text to numeric (INET_ATON / INET_NTOA to help) - get additional acceleration (and the tables will become more compact ...). - Akina