I have a database with about 40 tables that contains 'object data'.
These objects are loaded from files, so have an associated file.
Different files can contain the same object and be 'loaded'
simultaneously, but there can only be one instance of any object
at any one time. The active object is determined by the
Last-Modified time of the source file (newer objects take precedence
over older ones.)
Below is a simplified version of my DB along with some example
queries and further explanations about how I've been trying to do
this.
--------------------------------------------------------------------
CREATE DATABASE TestDB;
USE TestDB;
CREATE TABLE Files
(
FID SMALLINT UNSIGNED AUTO_INCREMENT,
LM DATETIME NOT NULL,
PRIMARY KEY (FID)
);
INSERT INTO Files VALUES (1,'1969-12-31 16:00:00'),
(2,'2002-10-30 14:45:16'),(3,'2002-07-17 16:59:22'),
(12,'2003-09-17 16:16:54'),(14,'2002-11-23 13:21:00');
CREATE TABLE Globals
(
FID SMALLINT UNSIGNED NOT NULL,
GID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Value CHAR(32) NOT NULL,
PRIMARY KEY(FID,GID)
);
INSERT INTO Globals VALUES (2,1,'Wrong'),(3,1,'Wrong'),
(1,4,'Correct!'),(2,2,'Wrong'),(2,3,'Wrong'),(12,1,'Correct!'),
(2,5,'Correct!'),(1,3,'Wrong'),(1,1,'Wrong'),(14,3,'Correct!'),
(1,5,'Wrong'),(1,2,'Wrong'),(12,2,'Correct!'),(3,2,'Wrong'),
(14,1,'Wrong');
--------------------------------------------------------------------
mysql> SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb
-> LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) ORDER BY Glb.GID,
-> Fil.LM DESC;
+-----+-----+----------+---------------------+
| GID | FID | Value | LM |
+-----+-----+----------+---------------------+
| 1 | 12 | Correct! | 2003-09-17 16:16:54 |
| 1 | 14 | Wrong | 2002-11-23 13:21:00 |
| 1 | 2 | Wrong | 2002-10-30 14:45:16 |
| 1 | 3 | Wrong | 2002-07-17 16:59:22 |
| 1 | 1 | Wrong | 1969-12-31 16:00:00 |
| 2 | 12 | Correct! | 2003-09-17 16:16:54 |
| 2 | 2 | Wrong | 2002-10-30 14:45:16 |
| 2 | 3 | Wrong | 2002-07-17 16:59:22 |
| 2 | 1 | Wrong | 1969-12-31 16:00:00 |
| 3 | 14 | Correct! | 2002-11-23 13:21:00 |
| 3 | 2 | Wrong | 2002-10-30 14:45:16 |
| 3 | 1 | Wrong | 1969-12-31 16:00:00 |
| 4 | 1 | Correct! | 1969-12-31 16:00:00 |
| 5 | 2 | Correct! | 2002-10-30 14:45:16 |
| 5 | 1 | Wrong | 1969-12-31 16:00:00 |
+-----+-----+----------+---------------------+
15 rows in set (0.00 sec)
--------------------------------------------------------------------
mysql> SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb
-> LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) GROUP BY Glb.GID;
+-----+-----+----------+---------------------+
| GID | FID | Value | LM |
+-----+-----+----------+---------------------+
| 1 | 2 | Wrong | 2002-10-30 14:45:16 |
| 2 | 2 | Wrong | 2002-10-30 14:45:16 |
| 3 | 2 | Wrong | 2002-10-30 14:45:16 |
| 4 | 1 | Correct! | 1969-12-31 16:00:00 |
| 5 | 2 | Correct! | 2002-10-30 14:45:16 |
+-----+-----+----------+---------------------+
5 rows in set (0.09 sec)
--------------------------------------------------------------------
mysql> SELECT Glb.GID, Glb.FID, Glb.Value, MAX(Fil.LM) FROM Globals
-> Glb LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) GROUP BY Glb.GID;
+-----+-----+----------+---------------------+
| GID | FID | Value | MAX(Fil.LM) |
+-----+-----+----------+---------------------+
| 1 | 2 | Wrong | 2003-09-17 16:16:54 |
| 2 | 2 | Wrong | 2003-09-17 16:16:54 |
| 3 | 2 | Wrong | 2002-11-23 13:21:00 |
| 4 | 1 | Correct! | 1969-12-31 16:00:00 |
| 5 | 2 | Correct! | 2002-10-30 14:45:16 |
+-----+-----+----------+---------------------+
5 rows in set (0.00 sec)
--------------------------------------------------------------------
mysql> CREATE TEMPORARY TABLE TmpGlobals SELECT GID, MAX(LM) as LM
-> FROM Globals LEFT JOIN Files ON(Globals.FID=Files.FID) GROUP
-> BY GID;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb
-> LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) LEFT JOIN TmpGlobals
-> TGlb ON(Glb.GID=TGlb.GID) WHERE Fil.LM=TGlb.LM ORDER BY
-> Glb.GID;
+-----+-----+----------+---------------------+
| GID | FID | Value | LM |
+-----+-----+----------+---------------------+
| 1 | 12 | Correct! | 2003-09-17 16:16:54 |
| 2 | 12 | Correct! | 2003-09-17 16:16:54 |
| 3 | 14 | Correct! | 2002-11-23 13:21:00 |
| 4 | 1 | Correct! | 1969-12-31 16:00:00 |
| 5 | 2 | Correct! | 2002-10-30 14:45:16 |
+-----+-----+----------+---------------------+
5 rows in set (0.00 sec)
--------------------------------------------------------------------
Goal:
mysql> ????
+-----+-----+----------+---------------------+
| GID | FID | Value | LM |
+-----+-----+----------+---------------------+
| 1 | 12 | Correct! | 2003-09-17 16:16:54 |
| 2 | 12 | Correct! | 2003-09-17 16:16:54 |
| 3 | 14 | Correct! | 2002-11-23 13:21:00 |
| 4 | 1 | Correct! | 1969-12-31 16:00:00 |
| 5 | 2 | Correct! | 2002-10-30 14:45:16 |
+-----+-----+----------+---------------------+
5 rows in set (?.?? sec)
--------------------------------------------------------------------
I'm trying to return the row (Globals.*), for every unique GID, that
has the latest Last-Modified date (Files.LM). This is a slightly
simplified version of my problem.
A GROUP BY doesn't seem to work, or even look like it will ever work,
as there appears to be no way to choose the row returned in a GROUP
BY clause.
The TEMPORARY TABLE solution appears to work, but it will be messy
in actual use, and may prove to be too slow. I'm still looking for
a 'single query solution', which may not be possible without
subqueries. Unfortunately, using MySQL 4.1 is not an option for me.
The database design can be altered, though, despite the brick-wall
i'm at in retrieving the data, it appears this is the best way to
store it.
Any help would be appreciated, as well as any speed tips, as speed
is going to be very important in this DB (The file list could get as
high as 2,000 and the Object count 50,000+. I initially wanted to
this as an InnoDB, but I needed FULLTEXT capability in several of
the tables and chose MyISAM.
Thanks,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
