how to improve the speed of the MYSQL ODBC 3.51 driver in windows XP?

I have a simple table with 3 records in it. Doing a select * from table with ODBC in C#.NET is extremely slow, on the magnitude of 20 seconds or more.

how to improve the speed of the MYSQL ODBC 3.51 driver in windows XP?

Here's the query:

public static string sTableQueryUngroup = 
  " (SELECT
       *
       ,(SELECT unit_param_desc 
         FR开发者_高级运维OM tr_config_unit_params 
         WHERE unit_param_id={TABLE_NAME}.unit_param_id 
           AND unit_id={UNIT_ID}) as unit_param_desc 
     FROM {TABLE_NAME} 
     WHERE unit_param_id IN 
       (SELECT unit_param_id 
        FROM tr_config_unit_params 
        WHERE unit_id={UNIT_ID}) " + " 
        AND " + " {COLUMN_NAME} " + " BETWEEN '{FROM_DATE}' and '{TO_DATE}')";

my first table tr_config_unit_params structure

unit_param_id 
unit_id  
unit_param_desc   
unit_param_opc_progid  
unit_param_host    
unit_param_link   
unit_param_data_type  
unit_param_type   
groupID    
tagID    

my second table (date wise) r20111010

         `unit_param_id` 
         `param_value` 
         `OPC-date ` 
         `param_quality` 
         `PC_date_logged` 

i have used the two tables from the first table(tr_config_unit_params ) column unit_param_id match with another table(r20111010) unit_param_id & select the data between two dates(sorted by PC_date_logged or OPC-date ). I want to select particular unit (like group of collection)ID information from the table.

---

That your problem is with the SQL and not with the ODBC driver is easy to establish, just try the query directly using an native MySQL tool which does not use the ODBC driver.

I am sure that you will find you query running equally slowly without the driver (which in a case such as this does very little).

I would say the problem is with your query - I find it extremely verbose and don't really see why you are running it this way, although your syntax makes it difficult to decide exactly what you are trying to do as far as I can see this query can be expressed and a simple closed join between two tables with out EITHER of the sub-selects.

Removing the sub-selects will radically increase you performance in this case. Beyond that take a look at @duffymo'S anwser and use EXPLAIN to see where some approriate indexes might help you.

---

I'd look at your query before I'd blame the driver.

EXPLAIN PLAN and see where your query is slow. If you see TABLE SCAN, it's on you.

Make sure variables in WHERE clauses have indexes applied.

---

Firstly - try to rewrite your query, change subquery with JOIN, for example -

SELECT *, p.unit_param_desc FROM <TABLE_NAME> t
  JOIN (SELECT * FROM tr_config_unit_params WHERE unit_id = <unit>) p
    ON t.unit_param_id = p.unit_param_id 
WHERE <COLUMN_NAME> BETWEEN <'{FROM_DATE}'> and <'{TO_DATE}'>;

...change texts in brackets <...> with exact values.