What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

Hello! In spryker package `spy_price_product_store` there is an index of `spy_price_product_store-i

UNBSW8S8K
UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet
edited October 2019 in Help

Hello!

In spryker package spy_price_product_store there is an index of spy_price_product_store-index-fk_pr_pro-fk_cur-fk_st, which adds indexes to every column. This is causing issues for us to insert product prices. What would be the solution?

«13

Comments

  • Unknown
    Unknown

    the index are usually named

  • Unknown
    Unknown

    you might be able to override them on project level

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    Will it also remove it?

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer

    What exactly is your problem with the index?

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet
    edited October 2019

    As usually these definitions are merged.

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    We have 1.5 mil. rows in spy_price_product_store, to do an insert for one row, due to this index, it takes 7 minutes

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer

    No, it’s not possible to remove indices via schema.xml

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet
    edited October 2019

    So Spryker fucked up big time.

  • Unknown
    Unknown

    🤔

  • Unknown
    Unknown

    i don’t think so

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    So, what would be the solution then?

  • Unknown
    Unknown

    currently checking something

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    As I quote:

    >

    No, it’s not possible to remove indices via schema.xml

  • Unknown
    Unknown

    removing the index can have impact on read-time performance

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    Really? (you think I’m stupid?) What’s the point of read if you can’t even insert.

  • giovanni.piemontese
    giovanni.piemontese Technical Lead @ Löffelhardt Spryker Solution Partner Posts: 871 🧑🏻‍🚀 - Cadet

    yes, right.. the index exist also on my project..
    but the table is spy_price_product_store not spy_price_product.

    But it has no bad insert time problem on our project (we don't use the standard propel import for price).

    This index has no sense!

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer

    Could you give a little more context to your issue?
    When does this happen, during a mass import of prices or when saving a single one through Zed UI?
    What database do you use?

    Regarding the remove possibility in schema.xml, this is not really an issue introduced by Spryker, but the result of how Propel works with auto migrations.

  • giovanni.piemontese
    giovanni.piemontese Technical Lead @ Löffelhardt Spryker Solution Partner Posts: 871 🧑🏻‍🚀 - Cadet

    the index is here defined -> vendor/spryker/price-product/src/Spryker/Zed/PriceProduct/Persistence/Propel/Schema/spy_price_product.schema.xml:64

  • Unknown
    Unknown

    i agree with @UL6DGRULR, we would need a bit more context to help find a suitable solution for your scenario

  • Unknown
    Unknown

    ok, assuming we talk about mass importing here

  • Unknown
    Unknown

    naive approaches:

  • Unknown
    Unknown

    most database systems allow you to temporarily suspend index inserting

  • Unknown
    Unknown

    another suggestion is to use bulk writing (there are examples in spryker-shop/suite)

  • Unknown
    Unknown
    edited October 2019

    iirc, since the indexes are named, you can also override the index definition

  • Unknown
    Unknown

    how did you determine that the index is the bottleneck?

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    We use bulk writing with 20 stores and 1 product.

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet

    It has 20 rows to insert. But we tested it with 1 row. And it took 7 minutes.

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer

    Then as @UJN2JRU4F already suggested, deactivate the index check, import all prices, reactivate it. This will build the index only once and not for every insert.
    MySQL: https://support.tigertech.net/mysql-large-inserts
    Postgres: https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html

  • UNBSW8S8K
    UNBSW8S8K Posts: 128 🧑🏻‍🚀 - Cadet
    edited October 2019

    It’s not mysql. It’s postgres…. (sorry, misread message)

  • Unknown
    Unknown
    edited October 2019

    there are links for both in the above

Categories