On 17.09.2012 12:55, Juha Heinanen wrote:
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.
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