Re: Need help optimizing a query

[Martin]

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.city, 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

Martin