Walt,

----- Original Message -----
From: "walt" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Saturday, October 12, 2002 11:20 AM
Subject: innodb not using correct index


> Is there a way to find out what index an sql query is using? I know you
can
> use explain, but those are just "possible" indexes that the query might
use.
> I'm trying to find out exacly what execution path it is taking. Something

EXPLAIN SELECT ... always tells what index, if any MySQL picked.

> similar to Oracle's "set autotrace on;"
>
> MySql version - MySQL-Max-3.23.52-1
> OS version - RedHat 2.4.18-SGI_XFS_1.1enterprise
>
> Here is the problem I'm having.
> I've got a simple query which is taking about 1 1/2 minutes to run.
>
> select several_fields from xray
> where create_date > '2002-10-03'
> and cust_id = 'TEST1';

The optimizer of upcoming 3.23.53 was tuned for these kinds of queries. It
might solve your problem. With good luck 3.23.53 is released October 17,
2002.

> I have the following indexes.
> X_CREATE_DATE(create_date, cust_id).
> X_CUST_ID(cust_id, download_file).
>
> If I run
> select several_fields from xray
> where create_date > '2002-10-03';
>
> It takes less the 10 seconds.
>
> If I run
> select several_fields from xray
> where  cust_id = 'TEST1';
>
> It also takes less than 10 seconds.
>
> If I run the query on my Oracle test server (sparc 20, dual 60mhz, 160mb
ram,
> old ass raid with 5400rpm scsi drives), it takes about 10 seconds to
return
> the 374 rows.
...

> Thanks!
> sql, query
> --
> Walter Anthony
> System Administrator
> National Electronic Attachment
> Atlanta, Georgia
> 1-800-782-5150 ext. 1608
>  "If it's not broke....tweak it"

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com




---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to