Hello.

Recently began to study indices in mysql. Faced the following question, the answer to which I could not find. There is a request for a sample in which the condition on the sum of the columns participates, say:

SELECT col1, col2 FROM table1 WHERE (col1+col2) > 100

How to create an index for this condition (col1 + col2) ? I suggested that you can do it like this:

 CREATE INDEX col1col2 ON table1(col1+col2);

But there are doubts about the correctness of such a record. Can anyone tell me how?

    2 answers 2

    no, you can't do that. It is simply impossible to apply an arithmetic operation when creating an index.

    if it is VERY necessary, you can make a new column that you will update with the values ​​of col1 + col2 and make it an index.

    values> N choose it.

    you can make a composite index (col1, col2), but it will be slower than the index on the column in which the amount is already stored.

     create table v (a int, b int, key (a,b)); insert into v values(1,2); explain select a+b from v; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | v | NULL | index | NULL | a | 10 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ alter table v drop index a; Query OK, 0 rows affected (0.14 sec) explain select a+b from v; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | v | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

    notice the difference in two explain

    It all depends on your requirements.

    • Without (composite) index - adishche.

    • With a composite index is better. // see postscript, maybe I'm not quite right, depends on the request

    • With a new column, there are more hdd losses and slower when inserting data, but much faster when reading.

    PS even more dependent on the request, see the answer @Firepro and comments to it.

    • Only he with the amount still does not help, right? - Alexey Shimansky
    • @ Alexey Shimansky unfortunately, MySQL, unlike PostgreSQL, does not support indexing of expressions. You can index only a column. - cheops
    • @ Alexey Shimansky can help with summation, it can be checked, by the way. IMHO better to make a new column. - strangeqargo
    • I have a composite index, here were doubts that it is suitable. It turns out the solution is only the creation of a new column. Well, thank you for shedding light :) - user3742070

    Unfortunately, there is no support for conditional indexes in MySQL, as it is in PostgreSQL.

    Creating a composite index on col1 and col2 will speed up the total query execution time, because the data is retrieved faster, but if you use other fields in the query, most likely the built-in optimizer will decide on a full table scan.

    • we checked and the composite index is used . - strangeqargo 10:08 pm
    • I did not say that the index is not used. It does not speed up the processing of the operation of addition, but only helps to choose the values ​​for this operation, so as not to scan the entire row, if among col1 and col2, there are still columns. On the old Pentium, 1,000,000 addition operations are performed in 0.3 s. - Firepro
    • Well, it will speed up all the same, because it will help you choose values. If you edit the answer, I agree with you. - strangeqargo
    • Indices for this purpose are intended)) But in this case, only if SELECT is associated with col1 and col2, if you do SELECT col1, col2, name, last_name, first_name WHERE col1 + col2> 100, then most likely, the built-in optimizer will use the full scan without index. - Firepro
    • And you are right. explain select a+b from y force index(a) where (a+b) > 1; Only so I could get Using where; Using index Using where; Using index , explain select a+b, yc from y force index(a) where (a+b) > 1; does not use it anymore - strangeqargo 10:49 pm