如何提高从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屋!