Mysql – Pull all parent records with or without child record – if child record exists select latest record

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
       ( SELECT *
         FROM tableX AS tt
         WHERE tt.widget = t.widget
           AND tt.state <> 'down'
           AND tt.`timestamp` > MAX(t.`timestamp`)
       ) ;

I think that you'll need two indices, one on (widget, state, timestamp) and one on (widget, timestamp, state) for efficiency.

This will work, too, and will be needing only one index, on (widget, timestamp, state):

SELECT t.widget, t.`timestamp`
FROM 
        tableX AS t
    JOIN
        ( SELECT widget, MAX(`timestamp`) AS ts
          FROM tableX
          GROUP BY widget
        ) AS tm
            ON  tm.widget = t.widget
            AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

Tested both at SQL-Fiddle: test

Consider an Entity-Attribute-Value design. The general concept is that you put all of the data in a very long, narrow table which may take the form of:

CREATE TABLE dbo.PropertyAttributes
(
PropertyID   INT NOT NULL
             REFERENCES dbo.Properties(PropertyID),
AttributeID  INT NOT NULL
             REFERENCES dbo.Attributes(AttributeID),
StringValue  NVARCHAR(1024),
NumericValue DECIMAL(16,4),
DateValue    SMALLDATETIME,
ModifiedDate SMALLDATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (PropertyID, AttributeID)
);

Then you store metadata about the attributes. At a minimum you'd need something like this:

CREATE TABLE dbo.Attributes
(
AttributeID INT PRIMARY KEY,
PropertyID  INT NOT NULL 
            REFERENCES dbo.Properties(PropertyID),
[Name]      NVARCHAR(32) NOT NULL UNIQUE,
DataTypeID  TINYINT NOT NULL -- 1 = string, 2 = numeric, 3 = date
);

Taking this approach offers some benefits because when new attributes show up in the data, you don't have to modify the database structure to house the data and you don't have to construct new queries to get at the new columns you created.

Like other aspects of life, these benefits come with tradeoffs. Creating a very wide view requires you to Pivot the table which can be an expensive query to run.

Aaron Bertrand, who posts here quite a bit, described his experience with this approach in 2009. It's still worth a read today.

MDCCL's suggestion to review attributes and potentially decompose the most relevant ones is a good one, move the most commonly used attributes into the base Properties table so you pivot the EAV table less frequently.