Website real estate bulletin board, a lot of filters (15-20) on the parameters of real estate. Each filter is one where clause in select, in the end, if you select 4-5 filters, we get 4-5 conditions in the select. There are text fields, such as street, district. Please tell me how to optimize this? Put indexes on all fields?

    3 answers 3

    You need to look deeper - how indexes work. MySQL selects only one index - which will be sampled. The index is an additional column in the table - not bad for reading if there are a lot of indexes. True recording will be slower - but usually tolerable.

    If in where we have 5 conditions for 5 fields - we will get the maximum increase when adding an index to all 5 fields. In principle, if we add indices for the most frequent samples, it will be quite fast on a volume of up to 10 mil. records.

    If more - you need to shard (cut into pieces) a table, or use NoSql solutions - which are searched very quickly for any fields (including not by exact coincidence) on huge volumes. For example, we used Apache Solr for a similar task - a bulletin board, declared the base 10 mil, were satisfied.

      20k records are a miser, even without indices everything will work at an acceptable speed.

      Ideally, see which queries will most often (which filters consist of), add indices for such fields, which will give the maximum reduction in the sample.

      For example, if the filter by street from 10k records selects 10, then the indices for the other fields are not needed. However, if the street is not involved in the filter, you will need a filter for other fields / field combinations.

        As an option - yes, it is possible (in the sense of making so many indices that the whole table will be covered by them). But it is better to work with the query planner, most likely some filters will be more often used plus, estimate the time for the request-materialization, most likely, the select is not the most resource-intensive. Those. sampling one table with a variety of conditions - in principle, not the hardest option, here join-sets of tables with subqueries - this is already a field for optimization. And so, how many records are we talking about?

        • About 20 thousand on one site, only 20 sites on one hosting, each with its own MySQL database - Dmitry Portnov