List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 7 2012 6:29pm
Subject:RE: Create a VIEW with nested SQL
View as plain text  
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: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
> 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
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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