itsecureadmin

mysql> select id,inverter_id,timestamp_poll from inverter_monitor_polls order by id desc limit 100;
+------------+-------------+---------------------+
| id         | inverter_id | timestamp_poll      |
+------------+-------------+---------------------+
| 2150109370 |        2090 | 2012-12-06 06:18:00 |
| 2150109369 |        2701 | 2012-12-06 06:18:00 |
| 2150109368 |        1248 | 2012-12-06 06:17:00 |
| 2150109367 |        2090 | 2012-12-06 06:17:00 |
| 2150109366 |      322694 | 2012-12-06 06:17:00 |
| 2150109365 |        1248 | 2012-12-06 06:16:00 |

itsecureadmin

| 3398201 | helios      | 10.180.148.162:35374                     | device_hub | Query       |        5 | Copying to tmp table                   
                               | SELECT `devices`.`id` AS `id`, `serial_numbers`.`serial` AS `serial_number`, `mac_address`, CAST(group_concat(CAS
E WHEN lookup_metrics.ui_metric_name = 'version_firmware_dsp' THEN inverter_properties.property_value ELSE null END) AS decimal(30,2)) AS `version
_firmware_dsp`, CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_pvm' THEN inverter_properties.property_value ELSE nu
ll END) AS decimal(30,2)) AS `version_firmware_pvm`, CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'pvm_status' THEN inverter_proper
ties.property_value ELSE null END) AS decimal(30,2)) AS `pvm_status`, `model_name`, `inverter_connection_history`.`state` AS `location_state`, `fi
rst_reported`, `last_reported`, group_concat(DISTINCT inverter_lookup_status.name ORDER BY inverter_lookup_status.type SEPARATOR ',') AS `stat

itsecureadmin

mysql> SHOW INDEXES FROM  inverter_status_history \G
*************************** 1. row ***************************
        Table: inverter_status_history
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 8032570
     Sub_part: NULL

itsecureadmin

+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                       | type   | possible_keys                                    | key              | key_len | ref                                                      | rows | Extra                                        |
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | devices                     | index  | NULL                                             | PRIMARY          | 4       | NULL                                                     | 4962 | Using where; Using temporary; Using filesort |
|  1 | SIMPL

itsecureadmin

+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                       | type   | possible_keys                                    | key              | key_len | ref                                                      | rows | Extra                                        |
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | devices                     | index  | NULL                                             | PRIMARY          | 4       | NULL                                                     | 4962 | Using where; Using temporary; Using filesort |
|  1 | SIMPL

itsecureadmin

EXPLAIN
SELECT `devices`.`id` AS `id`, `serial_numbers`.`serial` AS `serial_number`, `mac_address`, 
  CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_dsp' 
    THEN inverter_properties.property_value 
    ELSE null END) AS decimal(30,2)) AS `version_firmware_dsp`, 
  CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_pvm' 
  THEN inverter_properties.property_value ELSE null END) AS decimal(30,2)) AS `version_firmware_pvm`, 
  CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'pvm_status' 
    THEN inverter_properties.property_value 

itsecureadmin

#!/bin/bash

#
# Author: Josh Miller
# Date:   11/06/2012
#

# ensure username was entered
user=$1
if [ -z ${user} ]