Questions Select 1

[biondo68]

Hi Everyone,

So, can anyone tell me how to edit these select?

SELECT * FROM documenti, docinfo, mdoccepag2 WHERE documenti.d_id = docinfo.d_id AND docinfo.d_id = mdoccepag2.d_id AND mdoccepag2.d_studio = 'S' AND mdoccepag2.d_azienda = 'S' ORDER BY docinfo.d_data DESC, docinfo.d_id ASC

it's very slow queries .
I have tried to add index docinfo.d_dat and docinfo.d_id but the explain out don't use index in the sort

The explain out..

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE mdoccepag2 ALL PRIMARY NULL NULL NULL 387556 Using where; Using temporary; Using filesort
1 SIMPLE documenti eq_ref PRIMARY PRIMARY 4 portale.mdoccepag2.d_id 1
1 SIMPLE docinfo eq_ref PRIMARY PRIMARY 4 portale.documenti.d_id 1 Using where

Thanks for all

Biondo68

 

[TonyGroves]

Firstly, convert the comma-joins into explicit JOINS, which makes the sequence of events clearer:
[tt]
SELECT *
FROM
documenti
JOIN docinfo ON documenti.d_id = docinfo.d_id
JOIN mdoccepag2 ON docinfo.d_id = mdoccepag2.d_id
WHERE mdoccepag2.d_studio = 'S' AND mdoccepag2.d_azienda = 'S'
ORDER BY docinfo.d_data DESC, docinfo.d_id ASC
[/tt]
Then, check you have indexes for all the named fields. However, MySQL will only use a particular index if it reckons that using it will improve speed, so don't be too surprised if it doesn't appear in the "explain" result.