MySQL Forums
Forum List  »  Performance

Re: Need help optimizing a query
Posted by: Martin
Date: October 19, 2004 09:33AM

Thanks Mike and Kim

Looking into your answers and other sources, like mysql docs and other posts I managed to get this results

SQL-query: EXPLAIN SELECT orders.OrderId, DATE_FORMAT(orders.OrderDate, '%m/%d/%Y') as OrderDate, DATE_FORMAT(orders.InstallDate, '%m/%d/%Y') as InstallDate, orders.UserId, orders.InstallerId, orders.promotion, orders.flag, customers.customerid, customers.Name,, customers.class , status.StatusDesc, products.ProductDesc, time.TimeDesc FROM `orders`, `customers`,`status`, `products`, `time` WHERE orders.CustomerId = customers.CustomerId AND orders.StatusId = status.StatusId AND orders.ProductId = products.ProductId AND orders.TimeId = time.TimeId AND orders.InstallDate BETWEEN '2004-05-19' AND '2004-10-19' AND orders.statusid = 4 AND orders.timeid = 2 AND orders.userid = 184 AND orders.installerid = 129 AND orders.productid = 1 AND orders.branchid = 6 AND customers.stateid = 2 AND orders.groupid = 1 ORDER BY orders.orderdate ASC LIMIT 0, 30;
Rows: 5

table type possible_keys key key_len ref rows Extra
status const PRIMARY PRIMARY 4 const 1 Using filesort
products const PRIMARY PRIMARY 4 const 1
time const PRIMARY PRIMARY 4 const 1
orders ref CustomerId,InstallDate,TimeId,StatusId,ProductId,groupid StatusId 4 const 338 Using where
customers eq_ref PRIMARY PRIMARY 4 orders.CustomerId 1 Using where

I added all the posible "where" fields so you could see what are the tables I am picking info from.
The explanation looks a lot better now, but I am still concern. I used straight_join because I read some post about a database problem with queries hanging on "copying to tmp table..." and that he solved the problem with this straight_join.
The orders and customers tables have 8000+ records and I started noticing this problems since a month or so ago. I asked the hosting provider if there was a problem with the servers or something like that, knowing that they may have upgraded mysql but they said I was the only with that problem, so I looked to the processes everytime the site hanged and I found that this query (which is the main one) was allways in the described situation and I had to kill the process to recover (all the other processes were waiting or locked and nobody could work on the system). So, I am kind of lost. I used mybuilder to create the queries and everything worked fine until a few weeks ago.

Regarding indexes, I do have some of them, whit this thing going on, I was kind of crazy and I almost create an index for every field. I have the following indexes

Table orders
PK, Orderid. Individual indexes for Customerid, OrderDate (datetime), InstallDate (date), TimeId, StatusId, ProductId, claim (for search purposes) and GroupId. I also have a combined index like allfields(OrderId, CustomerId, OrderDate, InstallDate, TimeId, StatusId, ProductId, groupid, claim)

Most other tables have primary key on its tableid (like groupid on groups)

The other concern I have is that with so many changes being made to the query, I don't know if it is getting the right number of records. This changes everytime you take some value out or change the instruction, as you can imagine. What could I do to verify it?

Thank you so much guys for your help, I do really appreciate it


Options: ReplyQuote

Written By
October 18, 2004 10:43AM
October 18, 2004 11:27AM
October 18, 2004 08:53PM
Re: Need help optimizing a query
October 19, 2004 09:33AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.