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

UNBSW8S8K

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?

Unknown

the index are usually named

Unknown

you might be able to override them on project level

UNBSW8S8K

Will it also remove it?

Alberto Reyer

What exactly is your problem with the index?

UNBSW8S8K

As usually these definitions are merged.

UNBSW8S8K

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

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

UNBSW8S8K

So Spryker fucked up big time.

Unknown

🤔

Unknown

i don’t think so

UNBSW8S8K

So, what would be the solution then?

Unknown

currently checking something

UNBSW8S8K

As I quote:

>

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

Unknown

removing the index can have impact on read-time performance

UNBSW8S8K

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

giovanni.piemontese

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

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

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

Unknown

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

Unknown

ok, assuming we talk about mass importing here

Unknown

naive approaches:

Unknown

most database systems allow you to temporarily suspend index inserting

Unknown

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

Unknown

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

Unknown

how did you determine that the index is the bottleneck?

UNBSW8S8K

We use bulk writing with 20 stores and 1 product.

UNBSW8S8K

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

Alberto Reyer

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

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

Unknown

there are links for both in the above