Hello,
Currently, I have four tables (Items, UpdatePrice, UpdateStatus and
UpdateRelease). All the Update tables are linked to Items.ItemID via
Update(Price|Status|Release)ItemKey. Personally, I don't feel that
this is the best database design I could have, but I can't seem to
come up with one that'll work for me. I need to be able to add updates
to all three cases and still have access to a history of updates.
The problem(s) with this design is that I - in my opinion - get a very
slow result. Items consists of 500+ rows at the moment and the same
goes for the three Update tables. Those will obviously grow much
quicker. Another problem is that I can't seem to sort on the Items
table, while sorting on either of the Update tables seems for work.
This is my query for gathering all the data I need from all four tables:
SELECT * FROM Items t1
JOIN(SELECT * FROM UpdatePrice ORDER BY
UpdatePrice.UpdatePriceID DESC) AS t2 ON t1.ItemID =
t2.UpdatePriceItemKey
JOIN(SELECT * FROM UpdateStatus ORDER BY
UpdateStatus.UpdateStatusID DESC) AS t3 ON t1.ItemID =
t3.UpdateStatusItemKey
JOIN(SELECT * FROM UpdateRelease ORDER BY
UpdateRelease.UpdateReleaseID DESC) AS t4 ON t1.ItemID =
t4.UpdateReleaseItemKey
WHERE t1.ItemIsGame = 1
GROUP BY t1.ItemID
and then SORT BY t1.ItemTitle (doesn't work) or SORT BY
t2.UpdatePriceNew (does work).
http://grab.by/BWW - Screenshot of the query in case formatting is
lost in translation!
Basically, my questions are:
1) Is this a poor database design? If yes, how would you do it?
2) Is this a bloated query which can be perfected to work as intended
(mine doesn't) and perhaps faster?
Sincerely,
Eskil Kvalnes
eskil.kval...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
