MySQL Forums
Forum List  »  MySQL Workbench

Newbie Trying to Expedite Query
Posted by: Ron Mittelman
Date: May 30, 2013 05:11PM

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...

Options: ReplyQuote


Subject
Views
Written By
Posted
Newbie Trying to Expedite Query
1296
May 30, 2013 05:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.