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:[email protected]]
> Sent: Thursday, September 06, 2012 5:51 AM
> To: [email protected]
> 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
> [email protected]
> 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
