Osclass forums
Support forums => old => Topic started by: dinkar on October 12, 2015, 09:35:18 pm
-
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
-
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
-
@teseo
How can we also check for this problem ? What do i need to do ?
-
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:
SHOW INDEX FROM oc_t_item
(of course, change oc_ if you have changed default Osclass prefix)
Regards
-
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
-
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:
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 ?
-
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.
-
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.
# 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
-
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.
-
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, 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.
-
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.
-
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.
-
@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, 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
-
Kudos to @dinkar who discovered this !!!
-
1. Only oc_t_item is suffering from this (apparently not) ?
No, all the tables you see there as MySQL comments (starting with #)
2. ALL indexes in there ? I see
fk_c_currency_code
fk_i_category_id
fk_i_user_id
Each and everyone, you need to fill up up to xxxxxxxx_11 for each block. On the bright side, having right now 10 duplicates of everything, once cleaned up your database performance should be substantially improved.
Regards
-
I see:
oc_t_category
oc_t_city
oc_t_city_area
oc_t_item
oc_t_item_comment
oc_t_item_stats
oc_t_keywords
oc_t_plugin_category
and
oc_t_region
-
If anyone tries this REMEMBER to BACKUP first.
-
Index is not stored inside database, conventional backup does not work here (you are not removing anything from db).
-
Index is not stored inside database, conventional backup does not work here (you are not removing anything from db).
ok, i didn't knew that.
I test the script and seems to have worked. But now i'm opening the Indexes, i don't see any. Am i missing something ? Is this how it must be ?
See:
-
Index is not stored inside database, conventional backup does not work here (you are not removing anything from db).
I just wish to correct myself here, references to duplicate keys are stored inside it, of course, otherwise it wouldn't make sense. But, the index data itself is outside database. Creating a backup will keep the database with the old problem unsolved, but you could not find anything suspicious just by comparing ~ near the same db file size.
I test the script and seems to have worked. But now i'm opening the Indexes, i don't see any. Am i missing something ? Is this how it must be ?
I think that it looks fine to me, you have removed duplicates. You can run manual check with the SHOW INDEX query and you should get the exact same result listed in your screenshot (few posts above teseo gave an example).
-
Thanks for responding too fast
I have upgraded several times since 3.1
Is there any way to fix them
-
Thanks for responding too fast
I have upgraded several times since 3.1
Is there any way to fix them
The fix is here:
http://forums.osclass.org/3-5-x/database-issue-30878/msg131093/#msg131093
just see how many you have and add them (carefully with the correct numbers). Then goto your mysqladmin and run the SQL script.
Backup first !
-
I am successful in dropping the index but which one has to be kept like when i select the index
fk_i_parent_id then it automatically shifts to fk_i_parent_id_9
-
I am successful in dropping the index but which one has to be kept like when i select the index
fk_i_parent_id then it automatically shifts to fk_i_parent_id_9
The FIRST without a number is to be kept. This is what i did.
-
Yes, Its done and everything is working fine
Thanks to you All
-
Creating a small video is useful for us who are not a develpers, instead of solving issue we create more problems here.
-
Creating a small video is useful for us who are not a develpers, instead of solving issue we create more problems here.
I think you ask too much.