probaltam osszetett indexet is, nem segitett. Probaltam kivenni az extra parametert, nem segitett. Rohogni fogsz, de xenial + 10.2.16 egy hetzner vps-en gagyi eredmenyt ad, ld. fentebb. Viszont a xenial 10.2.16 egy vultr vps-en mar hasznalja az indexet ugyanannal a query-nel.
A kerdes tehat az, hogy lehet ravenni a mariadb-t, hogy a "select a,b,c from xxx where d in (1,2,3,4) query eseten hasznalja a d-bol keszitett indexet?
Szoval ez a mereg, nem a cian:
MariaDB [aaa]> explain extended select token, nham, nspam FROM token WHERE token in (1,2,3) ;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | token | ALL | token_idx1 | NULL | NULL | NULL | 5 | 60.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
MariaDB [aaa]> show index from token;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| token | 1 | token_idx1 | 1 | token | A | 5 | NULL | NULL | | BTREE | | |
| token | 1 | token_idx2 | 1 | uid | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Btw. a temp table-be iras nem jatszik, mert sok query mehet, akar parhuzamosan is.
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol