I have a bit of a performance/best practice question for those in the
know. I have a nested SQL statement that selects fields from a SELECT
that has a JOIN in it. Here's the SQL:
SELECT vLight.* FROM
(SELECT lights.*, machine.mach_name from lights
JOIN machine ON lights.mach_id = machine.mach_id
ORDER BY date DESC) as vLight
GROUP BY mach_id
Now, it's been a while for me to craft a complex SQL statement, so if
there is a better way, that's great. However, that's not really the issue.
I'm having a performance issue with this query because I'm using it to
pull data from the DB (read only) every 5 seconds or so to display
status lights from machines.
My thought was to make this a VIEW to see if that made a difference in
speed, but when I went to create it mySQL choked with an error about the
VIEW being built from a SELECT inside the SELECT. I googled a couple of
answers that moved the JOIN so it wouldn't be a nested SQL, and I
thought maybe I could build the initial SELECT (the internal one) as a
VIEW, than query that VIEW with the initial statement.
Then, of course, I realized that maybe none of this will give me the
boost I need, so I decided, in my vicodin soaked brain (I have a torn
rotator cuff and tendon in my shoulder) that I should hit the list
before I go any farther.
So, what's the best way, or the most common way to deal with this issue.
I'll be happy to clarify anything in here that doesn't make sense.
Thanks in advance.
--
Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
