如何提高从2个表中进行选择的SQL查询的性能 [英] How to increase perfromance of SQL query for selecting from 2 tables

问题描述

请帮助我，如何加快此sql查询的速度?

Please help me, how to speed up this sql query?

SELECT pa.*
FROM ParametrickeVyhladavanie pa, 
     (SELECT p.*
     FROM produkty p
     WHERE p.KATEGORIA IN ('$categoryArray')) produkt
WHERE produkt.ATTRIBUTE_CODE LIKE CONCAT('%', pa.code, '%')
AND produkt.ATTRIBUTE_VALUE LIKE CONCAT('%', pa.ValueCode, '%')
GROUP BY pa.code

索引: pa.code，pa.ValueCode，p.ATTRIBUTE_CODE，p.ATTRIBUTE_VALUE

Indexes: pa.code, pa.ValueCode, p.ATTRIBUTE_CODE, p.ATTRIBUTE_VALUE

显示第0-25行(共26行，查询用时 20.4995秒)

编辑 实际代码:

SELECT pa.*
FROM ParametrickeVyhladavanie pa
WHERE EXISTS
(
   SELECT 1 FROM produkty p
   JOIN 
   PRODUCT_INFO AS pi
   ON p.ProId = pi.ProduktID
   AND p.KATEGORIA IN ('Mobily'))

AND pi.ATTRIBUTE_CODE = pa.AttributeCode
AND pi.ATTRIBUTE_VALUE = pa.ValueCode
GROUP BY pa.code

此代码显示错误#1054 - Unknown column 'pi.ATTRIBUTE_CODE' in 'where clause' pi.表仅在(和)

This code says error #1054 - Unknown column 'pi.ATTRIBUTE_CODE' in 'where clause' The pi. table working only between ( and )

编辑-这是答案

我将MySQL 5.1更改为MariaDB 5.5及其更快的版本！！！

I changed MySQL 5.1 to MariaDB 5.5 and its faster!!!

推荐答案

不幸的是，您的数据库设计迫使性能降低.

Unfortunately your database design is forcing a slow performance.

这将解决您的性能问题: 您应该创建一个新表(PRODUCT_INFO)，并使外键指向产品主键. 用来自ATTRIBUTE_CODE和ATTRIBUTE_VALUE的各个值填充该表.

This will fix your performance problem: You should create a new table(PRODUCT_INFO) and make foreign key pointing to the primary key of produkty. Populate this table with the individual values from ATTRIBUTE_CODE and ATTRIBUTE_VALUE.

SELECT pa.code
FROM ParametrickeVyhladavanie pa
WHERE EXISTS
(
SELECT 1 FROM produkty p
JOIN 
PRODUCT_INFO AS pi
ON    p.ProId = pi.ProduktID
WHERE pi.ATTRIBUTE_CODE = pa.Code
AND pi.ATTRIBUTE_VALUE = pa.ValueCode
AND p.KATEGORIA IN ('Mobily'))
GROUP BY pa.code

这篇关于如何提高从2个表中进行选择的SQL查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！