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
