Newbie Trying to Expedite Query
I have a MySQL 5.6 database with one table called "history". I'm using it to monitor outputs of hardware elements. There are 650 columns. I know, lots of columns, but there are hundreds of inputs to monitor, and customers need to track values of each constantly. No way around this. Here is partial DDL:
CREATE TABLE `history` (
`record_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`record_id` int(11) NOT NULL AUTO_INCREMENT,
`run_date` date NOT NULL,
`run_number` tinyint(4) NOT NULL,
...
many columns here
...
PRIMARY KEY (`record_id`,`record_timestamp`),
UNIQUE KEY `record_id_UNIQUE` (`record_id`),
KEY `date_run` (`run_date`,`run_number`)
) ENGINE=InnoDB AUTO_INCREMENT=49477 DEFAULT CHARSET=utf8$$
Presently on my test system (my dev PC with Win 7 and local DB), the table has 49,476 rows.
I tried to query a particular date using the old table definition (missing the "KEY" index shown above) with this query:
select * from history where run_date='2013-05-02' and run_number=1;
This returns 39,030 rows, and takes > 30 seconds. (What the...???)
Figuring it was forcing a full table scan, I added the above "KEY" index, and run the query again. Basically the same exact results as before, > 30 seconds.
What I don't get is the output pane in Workbench shows:
"39030 row(s) returned 0.016 sec / 2.792 sec"
I don't understand the duration / fetch numbers at all. The query is taking > 30 seconds to complete and return results. Before I added the index, I think the duration number was about 0.250 sec, but don't remember exactly.
Since the index I added was equivalent to my WHERE clause, you'd think the query would return very quickly, not in over 30 seconds.
Any ideas what's wrong here? Thanks...