Otimizar consulta

[Thiago Moraes 32]

Ola, como posso otimizar a consulta abaixo?

SELECT DISTINCT  `jos_vm_product`.`product_id` , `product_name` ,  `products_per_row` ,  `category_browsepage` ,  `category_flypage` ,  `jos_vm_category`.`category_id` , `product_full_image` ,  `product_thumb_image` , `product_s_desc` ,  `product_parent_id` ,  `product_publish` , `product_in_stock` ,  `product_sku` ,  `product_url` , `product_weight` ,  `product_weight_uom` ,  `product_length` , `product_width` ,  `product_height` ,  `product_lwh_uom` , `product_available_date` ,  `product_availability` , `jos_vm_product`.`mdate` ,  `jos_vm_product`.`cdate` 
FROM (
`jos_vm_product` ,  `jos_vm_category` , `jos_vm_product_category_xref` ,  `jos_vm_shopper_group`
)
LEFT JOIN  `jos_vm_product_price` ON `jos_vm_product`.`product_id` = `jos_vm_product_price`.`product_id` 
WHERE  `jos_vm_product_category_xref`.`product_id` = `jos_vm_product`.`product_id` 
AND  `jos_vm_product_category_xref`.`category_id` = `jos_vm_category`.`category_id` 
AND (
(
`jos_vm_product`.`product_name` LIKE  '%caixa%'
OR  `jos_vm_product`.`product_sku` LIKE  '%caixa%'
OR  `jos_vm_product`.`product_s_desc` LIKE  '%caixa%'
OR  `jos_vm_product`.`product_desc` LIKE  '%caixa%'
)
AND (
`jos_vm_product`.`product_name` LIKE  '%transporte%'
OR  `jos_vm_product`.`product_sku` LIKE  '%transporte%'
OR  `jos_vm_product`.`product_s_desc` LIKE  '%transporte%'
OR  `jos_vm_product`.`product_desc` LIKE  '%transporte%'
)
)
AND (
(
`jos_vm_product`.`product_id` = `jos_vm_product_price`.`product_id` 
AND  `jos_vm_shopper_group`.`shopper_group_id` = `jos_vm_product_price`.`shopper_group_id`
)
OR  `jos_vm_product_price`.`product_id` IS NULL
)
AND  `jos_vm_shopper_group`.`default` =1
AND  `product_publish` =  'Y'
AND  `category_publish` =  'Y'
GROUP BY  `jos_vm_product`.`product_sku` 
ORDER BY  `jos_vm_product`.`product_name` ASC 
LIMIT 0 , 15

[Motta 645]

As tabelas tem índices ? Quais ?

Estatisticas atualizadas ?

Qual o tamanho das tabelas ?

[ConteudoAnimal.com.br 0]

Ola,

 

Troca distinct por unique que é menos lento

jos_vm_product_category_xref

Eu posso entender que VM = View Materializated ??

Se for a melhor solução é criar uma nova VM baseado nas clausulas where e ai apenas o select na nova VM

Tranforme esse select numa view .... pode ser que rode menos lento !