Query Not Functional

Hello,

I have this query. It works when I dont have many products (rows) but when tot total products reaches more then 30.000 (and counting) it takes to long to get the results. Mostly I quit the action, when I waited more then half an hour.

SELECT `webshop_product`.*
FROM `webshop_product`
WHERE (`webshop_product`.`parentId` = 0)
AND (deleted IS NULL)
AND
(
(
(
(
SELECT value
FROM webshop_product_attribute
INNER JOIN webshop_attribute
ON webshop_product_attribute.attributeId = webshop_attribute.id
WHERE webshop_product_attribute.productId = webshop_product.id
AND webshop_product_attribute.languageId = 1
AND webshop_attribute.name = 'name'
) LIKE '%test%'
)
)
OR (
SELECT GROUP_CONCAT(value)
FROM `webshop_category_attribute`
INNER JOIN `webshop_category_product` ON webshop_category_product.categoryId = webshop_category_attribute.categoryId
INNER JOIN `webshop_category` ON webshop_category.id = webshop_category_product.categoryId
WHERE (webshop_category.deleted IS NULL)
AND (webshop_category_product.productId = webshop_product.id)
AND (webshop_category_attribute.attribute = 'name'
) LIMIT 1
) LIKE '%test%'
)
ORDER BY (
SELECT `webshop_product_attribute`.`value`
FROM `webshop_product_attribute`
INNER JOIN `webshop_attribute` ON webshop_product_attribute.attributeId = webshop_attribute.id
WHERE (webshop_product_attribute.productId = webshop_product.id)
AND (webshop_product_attribute.languageId = 1)
AND (webshop_attribute.name = 'name')
) ASC

So I need to serach for the name and the category name. Not in this case but maybe I want also search trough other attributes like the price, description en even brand (other table).

I tried to use SQL_CALC_FOUND_ROWS and MATCH(prod_name) AGAINST (? IN BOOLEAN MODE), '*test*')

SELECT DISTINCT SQL_CALC_FOUND_ROWS webshop_product.id, `webshop_product`.*,
(
SELECT `webshop_product_attribute`.`value`
FROM `webshop_product_attribute`
INNER JOIN `webshop_attribute` ON webshop_product_attribute.attributeId = webshop_attribute.id
WHERE (webshop_product_attribute.productId = webshop_product.id)
AND (webshop_product_attribute.languageId = 1)
AND (webshop_attribute.name = 'visible')
) as "prod_visible",
(
SELECT `webshop_product_attribute`.`value`
FROM `webshop_product_attribute`
INNER JOIN `webshop_attribute` ON webshop_product_attribute.attributeId = webshop_attribute.id WHERE (webshop_product_attribute.productId = webshop_product.id)
AND (webshop_product_attribute.languageId = 1)
AND (webshop_attribute.name = 'name')
) as "prod_name"
FROM `webshop_product`
WHERE (webshop_product.deleted IS NULL)
AND (webshop_product.parentId = 0)
AND (MATCH(prod_name) AGAINST (? IN BOOLEAN MODE), '*test*')

When I use this, my mysql server crashes.

I hope you have enough info to give me a few tips. Maybe I must get all the products and search trough the results by PHP.

 

That's a nightmare query. Because of the way you're using nested queries everywhere, there's going to be virtually no way to speed it up to the point of it being usable. Can you explain the table structure and exactly what you're trying to pull.

Speculating here, but I would rethink the table structure. It appears that the data is being stored in a de-normalized manner and you're trying to hack in getting your data in a horizontal manner which is completely unrealistic. I would use separate queries or change the structure of the table so that you can pull the required data in a normalized and relational manner.

 

Dude,
You are making conditions inside query. Instead of that, use PHP for conditions.
Instead of creating a one big query, make them separate. Use PHP for applying logics.
That is a really nightmare query.

 

Thnx,

It is not easy to change the structure. A better idea is to use seperate querys.

So I create 1 query the get all of the products including the name, visibility and other info (attributes)
Then I use PHP to match the search key. (filter)

What time does it take to use PHP to match over 40.000 products? (and counting)

What do you say: Is this a sollution or not and change the structure?

 

I would change the structure, especially if you have more products coming in. This is going to be a nightmare for you to manage this. In the long term it is not that expensive.

 

Maybe you are right.

I will try to post the database tables tomorrow. Maybe you can help me to rearrange the tables.