List:General Discussion« Previous MessageNext Message »
From:Mark Haney Date:September 6 2012 12:50pm
Subject:Create a VIEW with nested SQL
View as plain text  
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
markh@stripped
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
Thread
Create a VIEW with nested SQLMark Haney6 Sep
  • Re: Create a VIEW with nested SQLhsv6 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
      • Re: Create a VIEW with nested SQLhsv8 Sep
      • RE: Create a VIEW with nested SQLRick James10 Sep
        • RE: Create a VIEW with nested SQLhsv11 Sep
  • RE: Create a VIEW with nested SQLRick James7 Sep