EXPLAIN on SQL Query

[Samantha Savvakis]

Hi,

System Specs:
SCO Openserver 5.0.6
MySQL 3.23.38

I have the following SQL Query (don't worry about the table aliasing)

SELECT
vdm1.model, vdm1.version, vdm1.date_ls_ro,
zdna1.code, zdna1.name, zdna1.title,
zdna1.phone_bus, zdna1.phone_home,
zdna1.pstl_adr_1, zdna1.pstl_adr_2,
zdna1.pstl_city, zdna1.pstl_state, zdna1.pstl_pcode,
idhs1.svc_doc, idhs1.rego,
idh1.branch, idl1.sale_code
FROM
VDM vdm1,
ZDNA zdna1,
IDHS idhs1,
IDH idh1,
IDL idl1
WHERE
vdm1.user_accnt = zdna1.code AND
vdm1.stock_no = idhs1.stock_no AND
idhs1.svc_doc = idh1.svc_doc AND
idh1.document = idl1.document AND
idhs1.svc_doc NOT LIKE 'S%' AND
(zdna1.pstl_pcode = '4303' OR
 zdna1.pstl_pcode = '4163' OR
 zdna1.pstl_pcode = '4036') AND
idl1.sale_code = 'LC' AND
idh1.branch = '01' AND
vdm1.date_ls_ro = '1999-01-01 00:00:00.00' AND
vdm1.date_ls_ro = '1999-01-19 00:00:00.00'
ORDER BY
idhs1.rego ASC

The following is the output from the EXPLAIN of this query: (I hope this
isn't hard to read)
**
table   |type   |possible_keys  |key|key_len   
 |ref|rows   |Extra


vdm1  |range  |key01,key11,a_stock_no,date_ls_ro|date_ls_ro |8  |  
 |98
|where used; Using temporary; Using filesort
zdna1   |eq_ref |key01,code |key01  |10 
|vdm1.user_accnt|1  |where used
idhs1   |ref  |key01,a_stock_no,a_svc_doc   |a_stock_no |14
 |vdm1.stock_no
|13 |where used
idh1|ref  |key01,a_svc_doc,document |a_svc_doc  |6 
 |idhs1.svc_doc  |18
|where used
idl1|ref  |key01,document   |document   |11
 |idh1.document  |17 |where used
***
This query doesn't perform too badly as it is using correct keys and the
number of rows it is required to sort through is minimal.

Now, I ran this query again, but changing the date in the where clause to be
the 20th of January instead of the 19th of Januaray:

vdm1.date_ls_ro = '1999-01-01 00:00:00.00' AND
vdm1.date_ls_ro = '1999-01-20 00:00:00.00'

This is the output of the EXPLAIN now:

table   |type   |possible_keys  |key|key_len   
 |ref|rows |Extra

---
vdm1|eq_ref |key01,key11,a_stock_no,date_ls_ro|key01|14 
|idhs1.stock_no
|1|where used
zdna1   |eq_ref |key01,code |key01  |10 
|vdm1.user_accnt  |1  |where used
idhs1   |eq_ref |key01,a_stock_no,a_svc_doc |key01  |6  |idh1.svc_doc  
 |1
|where used
idh1|eq_ref |key01,a_svc_doc,document   |key01  |11 
|idl1.document  |1
|where used
idl1|ALL|key01,document |   |  
 |   |650851 |where used; Using
temporary; Using filesort

Could by simply changing the date in this query alter the keys that are used
and the number of rows it is required to sort through? It doesn't make
sense.

Thanks,
Sam

--
Samantha Savvakis (B.E. Comp-Sys HONS)
Software Engineer
Computer Fiscal Services
Unit 1, 13-15 Steel Street
CAPALABA, BRISBANE Q4157
AUSTRALIA
Tel: 07 3245 7522
Fax: 07 3245 7299
www.cfs.net.au
www.autocheck.com.au



-
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

Preparing SQL Statements

[Samantha Savvakis]

Hi,

I was wondering if MySQL allows the ability to perform a SQL Prepare on sql
statements prior to performing an execute.

I'm using ADO on Windows and with a CommandPtr object, you can set a
property called Prepared, that will attempt to prepare sql statements and
will keep them prepared for future use so that executes run quicker. I am
also using MyODBC for the connection to my DB Server.

I have tried to use preparing of SQL statements, but am receiving this
error:

1006 Query  SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO
UNPREPARED STATE

It's not an invalid Select statement as these statements have worked for
months. So I'm wondering if it's MySQL, or perhaps MyODBC that does not
support preparing the sql statements. Previously, I have worked with other
databases using these ADO commands and this has not been a problem.

Thanks,
Sam



-
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

ADO problem with MYSQL datetime column

[Samantha Savvakis]

Hi,

I'm using ADO with the MyODBC driver.

I have a table that I'm querying that has 'datetime' columns. If these
columns are null or 0 - "-00-00 00:00:00", I have problems reading from
the ADO recordset.

I have other columns - char, varchar, integer etc that are null and don't
have this problem. It only seems to be the datetime columns.

I was wondering if this is an ADO issue with the MYSQL column type of
datetime, or perhaps an issue with the MyODBC driver and ADO ?

Has anyone found a work around for this problem? The only work around I've
got is to actually format the field when I select it, but this creates an
issue for me when I'm trying to retrieve all columns from a table that has
100+ columns. I wanted to avoid having to type out all column names in the
query.

Thanks,
Sam


-
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