17 Sep
2012
17 Sep
'12
12:45 p.m.
On 17.09.2012 12:55, Juha Heinanen wrote:
Klaus Darilion writes:
That's indeed more performance gain than I expected. But hard-coding the
NAT flag is a bit ugly. Unfortunately I do not have a better idea.
regards
Klaus
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.