Database Issue

[dinkar]

When I checked my database in phymyadmin
it shows;
  The indexes fk_i_category_id and fk_i_category_id_9 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_8 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_7 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_6 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_5 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_4 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_3 seem to be equal and one of them could possibly be removed.
  The indexes fk_i_category_id and fk_i_category_id_2 seem to be equal and one of them could possibly be removed.

For some of the Table, Is there anyone who can help me to fix this

[teseo]

Hi,

Is it possible that you had upgraded your Osclass installation up to 8 times?

I'm asking because I suspect that there is some bug on upgrade process, I have found this issue also in my databases, but only in those that I've upgraded at some point, the more upgrades the more the number of duplicated indexes.

This issue won't stop everything working, but I think that it might affect performance a bit, because MySQL has to determine which index should be used depending on the query.

Anyway, I know no way to massively delete indexes, so if you want to get rid of them, run these queries in PhpMyAdmin:

DROP INDEX fk_i_category_id_2 ON oc_t_item;
DROP INDEX fk_i_category_id_3 ON oc_t_item;
DROP INDEX fk_i_category_id_4 ON oc_t_item;
and so on...

Regards

[Aficionado]

@teseo

How can we also check for this problem ? What do i need to do ?

[teseo]

Those error messages you can see them in PhpMyAdmin, click on Structure in oc_t_item. There, + Indexes.

But if you run this query it'll show you all the indexes faster:

Code: [Select]

SHOW INDEX FROM oc_t_item
(of course, change oc_ if you have changed default Osclass prefix)

Regards

[dev101]

Yes, this is def a bug and seems to be related to upgrade procedure, where each adds new index.
There are other tables affected as well.

I have created an issue here:
https://github.com/osclass/Osclass/issues/1929

[Aficionado]

Those error messages you can see them in PhpMyAdmin, click on Structure in oc_t_item. There, + Indexes.

But if you run this query it'll show you all the indexes faster:

Code: [Select]

SHOW INDEX FROM oc_t_item
(of course, change oc_ if you have changed default Osclass prefix)

Regards

Hmmm, here is mine, am i in trouble ?

[dev101]

Index took 75% size of database itself in my case (~ empty dev site) so it definitely is a performance issue both ways. It's not natural state.

[teseo]

This issue won't stop everything working, but I think that it might affect performance a bit

After reading this article, I think now that "a bit" is an understatement:

https://www.percona.com/blog/2012/06/20/find-and-remove-duplicate-indexes/

Having duplicate keys in our schemas can hurt the performance of our database:
 
 They make the optimizer phase slower because MySQL needs to examine more query plans.
 
 The storage engine needs to maintain, calculate and update more index statistics
 
 DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load
 
 Our data needs more disk space so our backups will be bigger and slower

The origin of this problem seems to reside in original tables structure (struct.sql). For instance, indexes on oc_t_item:

        INDEX (fk_i_user_id),
        INDEX idx_b_premium (b_premium),
        INDEX idx_s_contact_email (s_contact_email(10)),
        INDEX (fk_i_category_id),
        INDEX (fk_c_currency_code),
        INDEX idx_pub_date (dt_pub_date),
        INDEX idx_price (i_price)

These indexes with no name allow duplicates being created at later time.

Here's a procedure to delete all detected duplicated indexes (This goes up to 3 of them, you'll need to add more or delete some depending on the number you really have).

Also, replace every prefix _oc if you are using a non-standard one.

Although deleting indexes won't affect your data, it's always wise to make a backup before executing this kind of a massive operation over your database.

Code: [Select]


# t_category
DROP INDEX fk_i_parent_id_2 ON oc_t_category;
DROP INDEX fk_i_parent_id_3 ON oc_t_category;
DROP INDEX fk_i_parent_id_4 ON oc_t_category;

DROP INDEX i_position_2 ON oc_t_category;
DROP INDEX i_position_3 ON oc_t_category;
DROP INDEX i_position_4 ON oc_t_category;


# t_city
DROP INDEX fk_i_region_id_2 ON oc_t_city;
DROP INDEX fk_i_region_id_3 ON oc_t_city;
DROP INDEX fk_i_region_id_4 ON oc_t_city;


# t_city_area
DROP INDEX fk_i_city_id_2 ON oc_t_city_area;
DROP INDEX fk_i_city_id_3 ON oc_t_city_area;
DROP INDEX fk_i_city_id_4 ON oc_t_city_area;


# t_item
DROP INDEX fk_c_currency_code_2 ON oc_t_item;
DROP INDEX fk_c_currency_code_3 ON oc_t_item;
DROP INDEX fk_c_currency_code_4 ON oc_t_item;

DROP INDEX fk_i_category_id_2 ON oc_t_item;
DROP INDEX fk_i_category_id_3 ON oc_t_item;
DROP INDEX fk_i_category_id_4 ON oc_t_item;

DROP INDEX fk_i_user_id_2 ON oc_t_item;
DROP INDEX fk_i_user_id_3 ON oc_t_item;
DROP INDEX fk_i_user_id_4 ON oc_t_item;


# t_item_comment
DROP INDEX fk_i_item_id_2 ON oc_t_item_comment;
DROP INDEX fk_i_item_id_3 ON oc_t_item_comment;
DROP INDEX fk_i_item_id_4 ON oc_t_item_comment;


# t_item_resource
DROP INDEX fk_i_item_id_2 ON oc_t_item_resource;
DROP INDEX fk_i_item_id_3 ON oc_t_item_resource;
DROP INDEX fk_i_item_id_4 ON oc_t_item_resource;


# t_item_stats
DROP INDEX dt_date_2 ON oc_t_item_stats;
DROP INDEX dt_date_3 ON oc_t_item_stats;
DROP INDEX dt_date_4 ON oc_t_item_stats;


# t_keywords
DROP INDEX fk_i_category_id_2 ON oc_t_keywords;
DROP INDEX fk_i_category_id_3 ON oc_t_keywords;
DROP INDEX fk_i_category_id_4 ON oc_t_keywords;

DROP INDEX fk_i_city_id_2 ON oc_t_keywords;
DROP INDEX fk_i_city_id_3 ON oc_t_keywords;
DROP INDEX fk_i_city_id_4 ON oc_t_keywords;


# t_plugin_category
DROP INDEX fk_i_category_id_2 ON oc_t_plugin_category;
DROP INDEX fk_i_category_id_3 ON oc_t_plugin_category;
DROP INDEX fk_i_category_id_4 ON oc_t_plugin_category;


# t_region
DROP INDEX fk_c_country_code_2 ON oc_t_region;
DROP INDEX fk_c_country_code_3 ON oc_t_region;
DROP INDEX fk_c_country_code_4 ON oc_t_region;

Regards

[Aficionado]

I think this should be addressed by Osclass team, for ALL users of Osclass, probably via a plugin ?

Because most users are prone for errors when forced to use directly sql commands via phpadmin or else. Most of them probably will not be able to do it at all.

[dev101]

Aficionado, you don't need a plugin, just run the query with example given above by teseo and you are set. MySQL will return errors if you try to remove indexes which do not exists, and each has different numbers of upgrade cycles.

[Aficionado]

Aficionado, you don't need a plugin, just run the query with example given above by teseo and you are set. MySQL will return errors if you try to remove indexes which do not exists, and each has different numbers of upgrade cycles.

It is not me i'm talking about, but for all Osclass users. They will not be able to do it. Most of them they can't find config.php location.

Also even to me, it is not clear what indexes should be removed.

[dev101]

Upgrade function will be provided to remove those duplicates, there is no simpler way than this. Since they are duplicate copies, it is technically irrelevant which one you decide to keep, but lowest index sounds logical.

[Aficionado]

Upgrade function will be provided to remove those duplicates, there is no simpler way than this. Since they are duplicate copies, it is technically irrelevant which one you decide to keep, but lowest index sounds logical.

During upgrade seems logical and for all users.

[teseo]

@Aficionado, judging by the screenshot you posted above, you'd need to add more queries to each table block I wrote. For instance, for t_category:

# t_category
DROP INDEX fk_i_parent_id_2 ON oc_t_category;
DROP INDEX fk_i_parent_id_3 ON oc_t_category;
DROP INDEX fk_i_parent_id_4 ON oc_t_category;
DROP INDEX fk_i_parent_id_5 ON oc_t_category;
DROP INDEX fk_i_parent_id_6 ON oc_t_category;
DROP INDEX fk_i_parent_id_7 ON oc_t_category;
DROP INDEX fk_i_parent_id_8 ON oc_t_category;
DROP INDEX fk_i_parent_id_9 ON oc_t_category;
DROP INDEX fk_i_parent_id_10 ON oc_t_category;
DROP INDEX fk_i_parent_id_11 ON oc_t_category;

Regards

[Aficionado]

@Aficionado, judging by the screenshot you posted above, you'd need to add more queries to each table block I wrote. For instance, for t_category:

# t_category
DROP INDEX fk_i_parent_id_2 ON oc_t_category;
DROP INDEX fk_i_parent_id_3 ON oc_t_category;
DROP INDEX fk_i_parent_id_4 ON oc_t_category;
DROP INDEX fk_i_parent_id_5 ON oc_t_category;
DROP INDEX fk_i_parent_id_6 ON oc_t_category;
DROP INDEX fk_i_parent_id_7 ON oc_t_category;
DROP INDEX fk_i_parent_id_8 ON oc_t_category;
DROP INDEX fk_i_parent_id_9 ON oc_t_category;
DROP INDEX fk_i_parent_id_10 ON oc_t_category;
DROP INDEX fk_i_parent_id_11 ON oc_t_category;

Regards

@Teseo

Thanks. Some questions.

1. Only oc_t_item is suffering from this (apparently not) ?

2. ALL indexes in there ? I see

fk_c_currency_code

fk_i_category_id

fk_i_user_id