[FC] some speedup for MAC Track

[gthe]

Sorry for my English...

In my mysql_slowquery.log I see constant records:

Code: Select all

# Time: 080117 10:48:06
# User@Host: cactiuser[cactiuser] @ localhost []
# Query_time: 80  Lock_time: 0  Rows_sent: 0  Rows_examined: 1532615
REPLACE INTO mac_track_scan_dates (SELECT DISTINCT scan_date from mac_track_ports);

The reason of their occurrence:

Code: Select all

mysql> SELECT DISTINCT scan_date from mac_track_ports;
+---------------------+
| scan_date           |
+---------------------+
| 2008-01-29 01:13:07 |
| 2008-01-29 01:22:27 |
| 2008-01-29 01:34:54 |
| 2008-01-29 01:43:33 |
| 2008-01-29 01:52:28 |
| 2008-01-29 02:07:40 |
| 2008-01-29 02:14:47 |
............................................
| 2008-02-12 18:06:39 |
+---------------------+
1738 rows in set (25.60 sec)

For optimization I have made:

Code: Select all

ALTER TABLE `mac_track_ports` ADD INDEX `scan_date` USING BTREE(`scan_date`);

And after that my results:

Code: Select all

mysql> SELECT DISTINCT scan_date from mac_track_ports;
+---------------------+
| scan_date           |
+---------------------+
| 2008-01-29 01:13:07 |
| 2008-01-29 01:22:27 |
| 2008-01-29 01:34:54 |
| 2008-01-29 01:43:33 |
| 2008-01-29 01:52:28 |
.......................................
| 2008-02-12 17:53:10 |
| 2008-02-12 18:06:39 |
| 2008-02-12 18:17:09 |
+---------------------+
1739 rows in set (0.01 sec)

From 25 to 0.01 sec!!

[streaker69]

Interesting: Mine went from 3.77 seconds to 0.04 seconds.

Nice.

[TheWitness]

Wow!

TheWitness

[chrisgapske]

I went from 10 seconds to .04?


| 2008-02-04 07:10:59 |
| 2008-02-04 07:25:46 |
| 2008-02-04 07:35:51 |
| 2008-02-04 07:50:54 |
| 2008-02-04 08:06:07 |
| 2008-02-04 08:20:41 |
| 2008-02-04 08:30:56 |
| 2008-02-04 08:40:57 |
| 2008-02-04 08:55:42 |
+---------------------+
2835 rows in set (0.04 sec)

[ceyounger]

Nice catch!

I showed this to my DBAs and they said, "Duh!"

I'm no DBA...

Chris

[msw1970]

I went from 46.30 seconds to 0.02!!!!

[TheWitness]

Ok, I feel pwn'd now...

Larry

[lavermil]

Ok, I feel pwn'd now...

Larry

Is this going to be rolled into the next release?

[dogs]

Can someone give me more info on this please. I'm not too sure how i go about changing this.

[chrisgapske]

I believe you have to just go to a mysql cli and type


ALTER TABLE `mac_track_ports` ADD INDEX `scan_date` USING BTREE(`scan_date`);

it will add an index to your mactrack database making the seraching much faster.

[dogs]

thanks chris.......

[TheWitness]

Committed to MacTrack 2.0 in Trunk.