Need Help optimizing stored procedure
I have a stored procedure that is the following:
DELIMITER $$
DROP PROCEDURE IF EXISTS `rsview`.`zoneData`$$
CREATE PROCEDURE `zoneData`(startDate DATETIME, endDate DATETIME)
SELECT sql_big_result concat(LEFT(d.TagName,9),lpad(substr(d.TagName,10,(locate(_latin1'_',d.TagName,10) - 10)),2,_latin1'0'),RIGHT(d.TagName,(LENGTH(d.TagName) - (locate(_latin1'_',d.TagName,10) - 1)))) AS TagName,
d.MAX,
d.MIN,
d.AVG
FROM (SELECT sql_big_result
MAX(f.Val) AS MAX,
MIN(f.Val) AS MIN,
format(AVG(f.Val),2) AS AVG,
t.TagName
FROM (tagtable t INNER JOIN floattable f ON (t.TagIndex = f.TagIndex))
WHERE (t.TagName LIKE 'ISB_ZONE_%_%' AND (f.DateAndTime >= startDate) AND (f.DateAndtime < endDate))
GROUP by t.TagName) AS d
group by concat(LEFT(d.TagName,9),lpad(substr(d.TagName,10,(locate(_latin1'_',d.TagName,10) - 10)),2,_latin1'0'),RIGHT(d.TagName,(LENGTH(d.TagName) - (locate(_latin1'_',d.TagName,10) - 1))))$$
DELIMITER ;
Following is the information about the floattable and the tagtable:
mysql> describe FloatTable;
+-------------+------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+-------+
| DateAndTime | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| Millitm | int(10) unsigned | NO | | 0 | |
| TagIndex | int(10) unsigned | NO | MUL | 0 | |
| Val | double | NO | | 0 | |
| Marker | char(1) | NO | | | |
| Status | char(1) | NO | | | |
+-------------+------------------+------+-----+---------------------+-------+
6 rows in set (0.36 sec)
mysql>
mysql> describe TagTable;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| TagName | varchar(255) | YES | | NULL | |
| TagIndex | smallint(6) | YES | MUL | NULL | |
| TagType | smallint(6) | YES | | NULL | |
| TagDataType | smallint(6) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.44 sec)
mysql>
mysql> show index from tagtable;
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| tagtable | 1 | Index_1 | 1 | TagIndex | A |
NULL | NULL | NULL | YES | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
1 row in set (0.30 sec)
mysql> show index from floattable
-> ;
+------------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
| floattable | 1 | Index_1 | 1 | DateAndTime | A |
467860 | NULL | NULL | | BTREE | |
| floattable | 1 | Index_2 | 1 | TagIndex | A |
40721 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
2 rows in set (0.42 sec)
mysql>
mysql> explain select sql_big_result
-> MAX(f.Val) as MAX,
-> MIN(f.Val) as MIN,
-> format(AVG(f.Val),2) AS AVG,
-> t.TagName
-> from (tagtable t inner join floattable f on (t.TagIndex = f.TagIndex))
-> where (t.TagName LIKE 'ISB_ZONE_%_%' and (f.DateAndTime >= '2005-11-20 07
:00:00') and (f.DateAndTime < '2005-11-21 07:00:00'))
-> group by t.TagName
-> ;
+----+-------------+-------+------+-----------------+---------+---------+-------
------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+-----------------+---------+---------+-------
------------+------+-----------------------------+
| 1 | SIMPLE | t | ALL | Index_1 | NULL | NULL | NULL
| 1230 | Using where; Using filesort |
| 1 | SIMPLE | f | ref | Index_1,Index_2 | Index_2 | 4 | rsview
.t.TagIndex | 6882 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+-------
------------+------+-----------------------------+
2 rows in set (0.13 sec)
mysql>
When I run this procedure... the query's take very very very long... like minutes. The floattable has about 7 million records in it and is growing continuously.
I'm fairly new to MySql as it probably shows. Is there anything I can do to this select statement to help speed up the data or am I stuck with what I have?
thanks in advance
ben schroeder