[sr-dev] db version of nat pinger

[Klaus Darilion]

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