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: Fri, 5 Feb 2016 20:03:32 +0000
From: Brian Candler <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: Vincent Magnin <[email protected]>, Carlos Vicente
<[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=windows-1252; format=flowed
On 05/02/2016 14:24, Vincent Magnin wrote:
> It seems these 2 minutes comes from phpmyadmin web interface. Same query from
> mysql command prompt is much faster:
>
> mysql> 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 | id | name | device | id | interface | address |
> +-----+-------+------+--------+-------+-----------+------------+
> | 656 | 40275 | br0 | 656 | 14565 | 40275 | ******* |
> +-----+-------+------+--------+-------+-----------+------------+
> 1 row in set (13.27 sec)
>
> mysql> 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 | id | name | device | id | interface | address |
> +-----+-------+------+--------+-------+-----------+------------+
> | 656 | 40275 | br0 | 656 | 14565 | 40275 | ******* |
> +-----+-------+------+--------+-------+-----------+------------+
> 1 row in set (0.01 sec)
Of course, running the second query after the first will have cached
some results.
Might be interesting to try "EXPLAIN SELECT" with those two queries?
------------------------------
Message: 2
Date: Sat, 6 Feb 2016 12:28:25 +0000
From: Vincent Magnin <[email protected]>
Subject: Re: [Netdot-users] slow DB query?
To: Brian Candler <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="iso-8859-1"
Hi Brian,
Here is the output:
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)
It seems a key is not used when I sort the results.
Regards,
Vincent
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 83, Issue 3
*******************************************
