1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query Not Functional

Discussion in 'MySQL' started by Peter Funke, Feb 7, 2013.

  1. #1
    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*')
    SEMrush
    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.
     
    Peter Funke, Feb 7, 2013 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    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.
     
    jestep, Feb 14, 2013 IP
  3. ankurTheKing

    ankurTheKing Member

    Messages:
    393
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    35
    #3
    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.
     
    ankurTheKing, Feb 14, 2013 IP
  4. Peter Funke

    Peter Funke Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #4
    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?
     
    Peter Funke, Feb 19, 2013 IP
  5. tyteen4a03

    tyteen4a03 Member

    Messages:
    30
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    45
    #5
    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.
     
    tyteen4a03, Feb 19, 2013 IP
  6. Peter Funke

    Peter Funke Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    Maybe you are right.

    I will try to post the database tables tomorrow. Maybe you can help me to rearrange the tables.
     
    Peter Funke, Feb 19, 2013 IP