Why is MySQL Data Connector taking so much longer than MySQL Query Browser?

Question

I have a very simple query (three where conditions; two equals, one between) from a single table and in MySQL Query Browser the query takes less than half a second to run, returning 8300 records. If I run the exact same query using the MySQL Data Connector (really just an OLEDB wrapper), it takes about 35 seconds.

The engine being used is MyISAM (if that matters). I'm using Visual Studio 2008 (also, if that matters). Edit: Using MySQL Data Connector 5.2.5. Edit, edit: Switching to MySQL Data Connector 6.0.3 (the latest) shaved it down to 29 seconds.

The query is:

select drh_data.reading_time, drh_data.raw_value, drh_data.float_value, drh_data.data_quality
from drh_data
where drh_data.site_id=202
and drh_data.device_id=7
and reading_time between '2009-04-08 11:15:01' and '2009-05-08 11:15:02'
order by drh_data.reading_time desc;

Any ideas?

Update: I finally got around to checking CPU usage (as suggested by an answerer) and found that 50% CPU time is used by my application. The VirtualPC box that's running MySQL (in CentOS) had 0% during those 20 (or so) seconds, so the problem is all on the client side. I also ran an Explain on the query, which came back with the following:

id select_type table type possible_keys        key        key_len  ref  rows Extra
1 SIMPLE drh_data range PRIMARY,idx_site_device_reading_receive  idx_site_device_reading_receive 11     7674 Using where

I'm tapped out here. Does anyone have any ideas to resolve this? I'm about to resort to breaking up the selects via LIMIT, but I don't think I should have to do that.

Answer 1

I've run into this problem before using ADO.NET vs the SQL Server Query Analyzer. When we ran a query from Query Analyzer the query executed subsecond, when we ran it from our web app it was taking upwards of 2 minutes. For whatever reason applying indexes to the table seemed to speed up data connection between our app and the database.

I don't know the operational differences between MySQl and SQL Server, but another thing that seemed to be happening in our case was that the execution cache was filled up and that was slowing execution on all uncached execution plans way down. We implemented a weekly job that clears that cache.

Answer 2

I'd suggest checking out what's doing the work for those seconds (CPU usage), is it mysql or is it your code or something else. You might also consider using EXPLAIN to see what's happening in one or the other case and compare those.