I've noticed that when developing queries involving complex joins on
tables I sometimes seem to get appallingly long times the first time I run
a query, but the second and subsequent time I run it - even substituting
a different value for a field value I'm matching on - the query runs
quickly.
Does mySQL do some behind-the-scenes reindexing the first time,
resulting in the speed differences I see? If so is there some way I can
force it to re-build indices periodically so I can ensure that queries are
generally fast? Is this the point I should be going out and buying a book
about mySQL? ;-)
Here'a an example query:
SELECT d.ifIndex, d.MAC, a.IPadd, d.nMACs, d.mtime, x.deviceID, x.ifIndex
FROM dot1d as d
LEFT JOIN MAC_connections as x
ON d.MAC = x.MAC
LEFT JOIN IP_MAC as a
ON a.MAC = d.MAC
WHERE x.deviceID=d.deviceID AND x.ifIndex=d.ifIndex AND
d.deviceID= {some value}
My MAC_Connections table is indexed on MAC, and IP_MAC is indexed on MAC.
mySQL Version is 3.22.32
regards,
--
John Stumbles [EMAIL PROTECTED]
I.T. Services Centre, University of Reading http://www.rdg.ac.uk/~visstmbl
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
never generalise
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
