Send Netdot-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Netdot-users digest..."
Today's Topics:
1. Re: slow DB query? (Brian Candler)
2. Re: slow DB query? (Vincent Magnin)
----------------------------------------------------------------------
Message: 1
Date: Sun, 7 Feb 2016 15:09:02 +0000
From: Brian Candler <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: Vincent Magnin <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=windows-1252; format=flowed
On 06/02/2016 12:28, Vincent Magnin wrote:
> mysql> EXPLAIN SELECT device.id, interface.id, interface.name,
> interface.device, ipblock.id, ipblock.interface, ipblock.address FROM
> ipblock, interface, device WHERE interface.id = ipblock.interface AND
> device.id = interface.device AND device.id = '656' order by ipblock.address;
> +----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+-----------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+-----------------------------+
> | 1 | SIMPLE | device | const | PRIMARY | PRIMARY | 8
> | const | 1 | Using index; Using filesort |
> | 1 | SIMPLE | ipblock | ALL | Ipblock6 | NULL | NULL
> | NULL | 3181463 | |
> | 1 | SIMPLE | interface | eq_ref | PRIMARY,interface1 | PRIMARY | 8
> | netdot.ipblock.interface | 1 | Using where |
> +----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+-----------------------------+
> 3 rows in set (0.00 sec)
>
> mysql> EXPLAIN SELECT device.id, interface.id, interface.name,
> interface.device, ipblock.id, ipblock.interface, ipblock.address FROM
> ipblock, interface, device WHERE interface.id = ipblock.interface AND
> device.id = interface.device AND device.id = '656';
> +----+-------------+-----------+-------+--------------------+------------+---------+---------------------+--------+-------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+-----------+-------+--------------------+------------+---------+---------------------+--------+-------------+
> | 1 | SIMPLE | device | const | PRIMARY | PRIMARY | 8
> | const | 1 | Using index |
> | 1 | SIMPLE | interface | ref | PRIMARY,interface1 | interface1 | 8
> | const | 30 | Using index |
> | 1 | SIMPLE | ipblock | ref | Ipblock6 | Ipblock6 | 9
> | netdot.interface.id | 176747 | Using where |
> +----+-------------+-----------+-------+--------------------+------------+---------+---------------------+--------+-------------+
> 3 rows in set (0.00 sec)
That's pretty clear - it's a mysq optimiser bug and should go to a mysql
list (and/or check the version you're using, and whether it's improved
in a newer version of mysql)
It might be possible to rewrite the query, e.g. with subselects, to
force the optimiser to handle it differently; but that would just be a
workaround.
I do notice however that there's a superfluous join on the device table,
given that there's only one possible row which can match, and no other
columns from the device table are used in the result. What if you change
it to this simpler one?
EXPLAIN SELECT '656', interface.id, interface.name, interface.device,
ipblock.id, ipblock.interface, ipblock.address FROM ipblock, interface WHERE
interface.id = ipblock.interface AND interface.device = '656' order by
ipblock.address;
(I suspect the query optimiser has dealt with this properly though, due
to the 'const' reference)
Regards,
Brian.
------------------------------
Message: 2
Date: Sun, 7 Feb 2016 19:55:28 +0000
From: Vincent Magnin <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="iso-8859-1"
Hi Brian,
> check the version you're using, and whether it's improved in a newer version
> of mysql.
It's mysql from RHEL6 server package (mysql-server-5.1.73-5.el6_6.x86_64). I'll
try to upgrade my install to RHEL7 next week to see if it'll improve this query.
mysql> EXPLAIN SELECT '656', interface.id, interface.name, interface.device,
ipblock.id, ipblock.interface, ipblock.address FROM ipblock, interface WHERE
interface.id = ipblock.interface AND interface.device = '656' order by
ipblock.address;
+----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+----------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+----------------+
| 1 | SIMPLE | ipblock | ALL | Ipblock6 | NULL | NULL
| NULL | 3191656 | Using filesort |
| 1 | SIMPLE | interface | eq_ref | PRIMARY,interface1 | PRIMARY | 8
| netdot.ipblock.interface | 1 | Using where |
+----+-------------+-----------+--------+--------------------+---------+---------+--------------------------+---------+----------------+
2 rows in set (0.01 sec)
Regards,
Vincent
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 83, Issue 4
*******************************************
