Help on Php mysql query

[Nollyvenon]

Please can someone help me optmize this query, it is slow and brings the same data for the debtage column
SELECT customer.custid,customer.compname,customer.phone,customer.depot,acctofficer.acctfullname AS acctoff, STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y') AS purchdate, STR_TO_DATE(transactions.timestamp,'%Y-%m-%d') AS debtage, format( customer.balance, 2) AS balance, customer.balance AS balance1 FROM customer,mastorders,acctofficer,transactions WHERE customer.custid=mastorders.custid and acctofficer.acctoffid=customer.acctoff AND customer.depot='$depot' GROUP by customer.id ASC.

Thanks for your help.

[Fernando_4]

EXLPLAIN SELECT
customer.custid, customer.compname, customer.phone, customer.depot, 
acctofficer.acctfullname AS acctoff,
STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y') AS purchdate,
STR_TO_DATE(transactions.timestamp,'%Y-%m-%d') AS debtage, 
format( customer.balance, 2) AS balance, 
customer.balance AS balance1 
FROM 
customer, mastorders, acctofficer, transactions 
WHERE 
customer.custid = mastorders.custid 
AND acctofficer.acctoffid = customer.acctoff 
AND customer.depot = '$depot' 
GROUP by customer.id ASC

Go into your database manager (phpMyAdmin for example) and run the query I just posted and post the result table here, just so we can see what's happening in term of indexes.

Don't forget to format the result, this website allows you to make things more readable, make use of it or others might not even bother reading, as you didn't bother making it readable.

[Nollyvenon]

this is the result of the query

id     select_type     table     type    possible_keys     key     key_len      ref     rows     Extra 
 1     SIMPLE     acctofficer     ALL    NULL               NULL   NULL         NULL    8     Using\ temporary;\ Using\ filesort 
 1     SIMPLE     customer        ALL    NULL               NULL   NULL         NULL    11     Using\ where;\ Using\ join\ buffer 
 1     SIMPLE     mastorders      ALL    NULL               NULL   NULL         NULL    20     Using\ where;\ Using\ join\ buffer 
 1     SIMPLE     transactions    ALL    NULL               NULL   NULL         NULL    450     Using\ join\ buffer 

[Fernando_4]

Ok, if you check the rows column, you're checking all those rows in each table so in total you're scanning through 8 * 11 * 20 * 450 = 792000 rows.

If you're using phpMyAdmin go into structure view for each table and click 'Action' -> 'More' -> 'Add index' (or google "add key" or "add index" for your dbms) to the following columns.

customer.acctoff
customer.depot
customer.id
customer.custid
masterorders.custid
acctofficer.acctoffid

Hopefully this will make the query faster, you'll start to see the type and possible_keys columns filled in with values.

As for the debtage column I can´t find it in your query, so I've got nothing for you on that.

[Nollyvenon]

Thanks for your help but the record gotten seems to be the first instance, i wish to get the last records for transactions.timestamp and mastorders.lastsuppdate

my present query is:

SELECT
customer.custid, customer.compname, customer.phone, customer.depot,customer.`acctoff`,
STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y') AS purchdate,
STR_TO_DATE(transactions.timestamp,'%Y-%m-%d') AS debtage,
customer.balance AS balance
FROM
customer, mastorders, transactions
WHERE
customer.custid = mastorders.custid
AND customer.custid = transactions.custid
AND transactions.parttype='Receipt' AND customer.depot = 'National'
GROUP BY customer.id ASC

[Nollyvenon]

Just sorted it with

SELECT
customer.custid, customer.compname, customer.phone,customer.depot,customer.acctoff,MAX(STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y')) AS purchdate,MAX( DISTINCT STR_TO_DATE(transactions.timestamp,'%Y-%m-%d'))AS debtage,customer.balance AS balance
FROM
customer, mastorders, transactions
WHERE
customer.custid = mastorders.custid
AND customer.custid = transactions.custid
AND transactions.parttype='Receipt' AND customer.depot = 'National'
GROUP BY customer.id ASC

[Fernando_4]

If you only want the MAX purchdate and MAX debtage try placing it in the conditions.

WHERE
customer.custid = mastorders.custid
AND customer.custid = transactions.custid
AND transactions.parttype='Receipt' AND customer.depot = 'National'
AND purchdate = MAX(purchdate)
AND debtage = MAX(debtage)
GROUP BY customer.id ASC

Not sure if this will work, but it might give you an idea. Can you get a SQLFiddle going with some data for us to test?

Don't forget to try adding those keys to make the query faster, it won't change the outcome, but it will make it faster.

[Nollyvenon]

Thanks so much.

Finally got it working using:
SELECT
customer.custid, customer.compname, customer.phone, customer.depot,customer.acctoff,
MAX(STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y')) AS purchdate,
MAX( DISTINCT STR_TO_DATE(transactions.timestamp,'%Y-%m-%d'))AS debtage,
customer.balance AS balance
FROM
customer, mastorders, transactions
WHERE
customer.custid = mastorders.custid
AND customer.custid = transactions.custid
AND transactions.parttype='Receipt' AND customer.depot = '$depot'
GROUP BY customer.id ASC