[Apologies if this message hits the list twice: I sent it several
hours ago but didn't see it on the list or get a bounce message (or get
any response!) so I don't know what's happened to it.]
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, or
cache tables or indices, resulting in the speed differences I see? If so
is there some way I can periodically force it to do whatever it's doing 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
