VIEWs have never been optimized well. Avoid them.
Please provide SHOW CREATE TABLE for each table.
Is `machine` used for anything other than the machine_name?
Are you expecting one row?
Here's another way:
( SELECT mach_name
WHERE mach_id = lights.mach_id ) AS mach_name
ORDER BY lights.date DESC
lights would need INDEX(date)
machine would need INDEX(mach_id) -- Presumably it is the PRIMARY KEY, which suffices.
> -----Original Message-----
> From: Mark Haney [mailto:markh@stripped]
> Sent: Thursday, September 06, 2012 5:51 AM
> To: mysql@stripped
> Subject: Create a VIEW with nested SQL
> 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
> 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
> I'll be happy to clarify anything in here that doesn't make sense.
> Thanks in advance.
> Mark Haney
> Software Developer/Consultant
> AB Emblem
> 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