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 lights.*, ( SELECT mach_name FROM machine WHERE mach_id = lights.mach_id ) AS mach_name FROM lights ORDER BY lights.date DESC LIMIT 1; 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:ma...@abemblem.com] > Sent: Thursday, September 06, 2012 5:51 AM > To: mysql@lists.mysql.com > 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 > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql