17 Sep
2012
17 Sep
'12
6:55 a.m.
Klaus Darilion writes:
Not sure if an index would be much faster here. Also
the cflags & 0 = 0
should be rather fast.
klaus,
as i wrote, if an operation is done on index field, the index cannot be
used:
mysql> explain select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from
location where cflags & 16 = 16 and id % 20 = 0;
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | location | ALL | NULL | NULL | NULL | NULL | 10035 |
Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
mysql> explain select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from
location where cflags >= 15 and id % 20 = 0;
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | location | range | cflags_idx | cflags_idx | 4 | NULL | 1309
| Using where |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
in terms of execution time, the latter is about half of previous.
-- juha