can I select from one table only where some records exist in anothertable?
Sorry, that's not a very good formulation of the question. I have 2 tables, and I want to retrieve data from records in table A only if there are corresponding records in B. I don't care what's in the records in table B: all I want to know is which items in A _have_ records in B. If I do: select distinct A.this, A.that from A, B where A.key=B.key order by A.key then the join produces zillions of records and the query takes ages. Similarly if I do select distinct A.this, A.that from A left join B on A.key=B.key where B.key is not null it takes ages. I guess if I could somehow apply 'limit 1' to the join, so that I just get any one record from B (I don't care which record: after all I'm not using data from B) then it would go like greased lightning, but I can't see how to express that in mySQL SQL. Is this one of those things that can't be done without sub-selects? Something like: select A.this A.that from A where (select count(*) from B where B.key=A.key)0 (I'm not sure if that's valid SQL, but I hope it conveys my meaning.) 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
What causes table corruption? (isamchk -r required)
3 times in the last few days I've had tables that have become corrupted and needed isamchk -r to repair them. I'm becoming concerned: what can cause such errors? (and what can I do to avoid them?) I'm running 3.22.32 on Solaris 5.6 on a sparc ultra-1, and have processes updating the tables concerned continuously, via REPLACE INTO, INSERT INTO or UPDATE statements. TIA 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
How to join interdependant tables?
I have these tables: table fields a IPadd, MAC u IPadd, MAC, user si IPadd, deviceID, ifIndex sf deviceID, ifIndex, MAC For any given values of IPadd and MAC in 'a' there may or may not be records in 'u', 'si' and 'sf' For each IPadd and MAC in a I want to find the corresponding 'user' in 'u' (or null if there is none), so I do a left join: SELECT a.IPadd, a.MAC, u.user FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC I also want corresponding records from si and sf where a.IPadd=si.IPadd AND si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex AND a.MAC=sf.MAC If I simply left join tables si and sf: SELECT a.IPadd, a.MAC, u.user, si.deviceID FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC LEFT JOIN si ON a.IPadd=si.IPadd LEFT JOIN sf ON a.MAC=sf.MAC and add: WHERE si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex then I don't get any records from 'a' for which there are no records in si and sf; on the other hand if I put the extra conditions in the last 'ON': SELECT a.IPadd, a.MAC, u.user si.deviceID FROM a LEFT JOIN u ON a.IPadd=u.IPadd AND a.MAC=u.MAC LEFT JOIN si ON a.IPadd=si.IPadd LEFT JOIN sf ON a.MAC=sf.MAC AND si.deviceID=sf.deviceID AND si.ifINdex=sf.ifIndex then I get records from 'si' and 'sf' which don't match the 'IPadd' and 'MAC' from 'a'. How can I construct a SELECT to do what I want? 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
Why does 1st query take so long?
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
