1.
product_idint(11)
product_namevarchar(64)
product_update_dateint(11)
product_parameters_md5varchar(32)
part_idvarchar(255)

Indexes:

product_parameters_md5 UNIQUE product_parameters_md5
index_product_id INDEX product_id
index_product_name INDEX product_name
index_product_update_date INDEX product_update_date
index_part_id INDEX part_id

2. idint(11) auto_increment
iproduct_idint(11) icategory_idint(11) imanufacturer_idint(11) idistributor_idint(11) product_namevarchar(64)
quantityint(11) packagingvarchar(255)
wholesale_pricedecimal(12,2) s_detailsvarchar(255)
detailstext
sell_in_online_storeint(1)
special_offerint(1) special_offer_detailstext
posteddatetime
activeint(1) premierint(11) part_alertint(1) cache_statusint(1) product_parameters_md5varchar(32)

Indexes:

PRIMARY PRIMARYid
idx_c INDEX icategory_id
idx_m INDEX imanufacturer_id
idx_date INDEXposted
idx_d INDEX idistributor_id
idx_md5 INDEX product_parameters_md5

I have a query joining these tables:

SELECT DISTINCT cdn.product_id, cdn.product_parameters_md5, cdn.product_name, cdn.part_id, cdn.product_update_date
FROM jos_dn_cache_inventory_partid_asc cdn, jos_dn_inventory dn
WHERE cdn.product_parameters_md5 = dn.product_parameters_md5
AND dn.quantity >0
AND dn.icategory_id =1
ORDER BY cdn.part_id
LIMIT 0 , 30

The problem here is the ORDER BY cdn.part_id without sorting or without Distinct the query works thousand times faster but I need to have both.

id¦select_type¦table¦type¦possible_keys¦key¦key_len¦ref¦rows¦Extra
1¦SIMPLE¦cdn¦ALL¦product_parameters_md5¦NULL¦NULL¦NULL¦35018¦Using temporary; Using filesort
1¦SIMPLE¦dn¦ref¦idx_c,idx_md5¦idx_md5¦34¦cdn.product_parameters_md5¦1 ¦Using where; Distinct

If you look closer the first table has primary index product_parameters_md5 and it would usually make sense to add the part_id field to that index but the primary index cannot consist of more than one field.

Please advise how to solve this issue. Thanks!