Questions tagged [mysql-indexes]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
16 views

Choosing wrong mysql strategy for Catalog Price Rule Select statment

I am experiencing a bizarre issue to which I can't really wrap my head around, maybe someone here has faced a similar issue and has a fix or an idea. We are using Magento 2.3.5-p1, with a catalog from ...
user avatar
1 vote
2 answers
353 views

Add INDEX to Magento 2 tables?

My Sysadmin says there's some space for optimization of DB by adding INDEX to some tables. Namely: catalog_product_entity catalog_product_entity_varchar Has anybody tried this before? Any guidance on ...
user avatar
1 vote
0 answers
564 views

Error "DDL statements are not allowed in transactions" while create new product In Magento 2.3.4

When i try to create new product it showing below error "DDL statements are not allowed in transactions" I have done research and found that in "vendor/magento/framework/DB/Adapter/Pdo/...
user avatar
1 vote
1 answer
429 views

Can't create unique and fulltext index on the same column

My table in db_schema looks like below. <table name="test"> <column xsi:type="int" identity="true" name="test_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/> ...
user avatar
  • 11
4 votes
1 answer
333 views

Magento 1 on Percona DB produces errors

We are trying to migrate our DB to MySQL 5.7 on Percona XtraDB cluster. In the MySQL log file there are errors preventing some operations to be performed. The errors visible with the current setting ...
user avatar
  • 115
2 votes
2 answers
8k views

AddIndex in magento2

What is the use of index in table creation in magento2 ->addIndex($installer->getIdxName('blog_comment', ['parent_id']), ['parent_id']) Thanks
user avatar
  • 2,460
0 votes
1 answer
744 views

Magento 1.9.3.9 MySQL Database error

I'm getting some error from Magento MySQL database. I tried to fix it from "Magento Database Repair Tool" , unfortunately it shows "reference DB doesn't seem to be a valid Magento database". Please ...
user avatar
  • 1
1 vote
0 answers
60 views

Wrong products show up on substore main page?

We use Magento ver. 1.9.3.6 We have multiple substores in our shop In substore with store_id=3 products must be shown from cat_id=233 In CMS page I added: {{block type="catalog/product_list" name="...
user avatar
3 votes
2 answers
1k views

Magento indexing causing website downtime

Magento is creating 700 + connections leading to database breakdown whenever cache is flushed or indexing is triggered. Production site remain down for 20 mins till all connections clears. All ...
user avatar
0 votes
2 answers
378 views

How to allow apostrophe (') in Magento attribute

I have a create attribute called acloth'size for product and I have enabled the Use In Search Results Layered Navigation option in the backend. So while doing search, I am getting MySQL error a:5:{i:...
user avatar
0 votes
1 answer
948 views

Add a MySQL index key to a column in Magento 1 flat tables

How can I have the Magento product flat tables (catalog_product_flat_1 etc) built with a MySQL index (key) on a specific column? It seems that by default each flat table has these indexes: PRIMARY ...
user avatar
  • 111
0 votes
1 answer
383 views

ERROR The table 'catalog_product_index_price_bundle_sel_tmp' is full

There are 10k products, websites (over 50 ) and multiple store (i.e. over 100) in my web-store and I am getting following error during magento re-indexing process. Next exception '...
user avatar
2 votes
2 answers
989 views

How to reduce Magento Product Prices reindexing time for 1M product with 5K customer group?

I'm working with magento 1.9.2 website for a manufacture company which has over 1M product (each product have 5+ customer grouping price and trier pricing) and 5k+ customer group. I did all the basic ...
user avatar
  • 800
0 votes
1 answer
75 views

Magento 2.1 Cron Job MYSQL

I am having an issue with a cron job in Magento 2.1. I have a job that I need to query a database to get some stock values for my Magento products, but the cron job gives me the following error: HP ...
user avatar
  • 125
0 votes
1 answer
626 views

Reduce the size of the CL tables

I would like to reduce the size of the _cl tables in EE 1.14 catalog_product_index_price_cl | 1572.00 | | catalogsearch_fulltext_cl | 1512.00 | | ...
user avatar
1 vote
1 answer
484 views

what is mean by $indexes in installschema?? what it do?

as i was creating new module in magento2 which has table so when i was going through existing modules i found this function $indexes= array(); we are just creating array ok fine. but if you go ...
user avatar
1 vote
1 answer
106 views

Add index for import

To speed up the import process we added the index: ALTER TABLE catalog_product_entity_varchar ADD INDEX IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_VALUE (value ASC); After these changes the "search product" ...
user avatar
1 vote
0 answers
125 views

Magento 1.9.0.1 - Category URL Rewrites - Cannot intitialize the indexer process [duplicate]

I finally got my local installation of Magento 1.9.0.1 with the sample data up and running - XAMPP 1.8.3 on Windows 7 (Apache 2.4, PHP 5.5.15). Everything was working fine with all the status warning ...
user avatar
  • 11
2 votes
1 answer
878 views

Checkout Error when confirming new order (MySQL server has gone away)

I am having some issues with my Magento site. I have 2 backend guys on it and they cant get to the bottom of it. It started off with this error this morning Error: SQLSTATE[HY000]: General error: ...
user avatar
  • 21
0 votes
1 answer
613 views

Reindex issues with Price, URL and Search

I've been trying to update my magento index and a few are few but I get the following error for 3 of them Product Prices index process unknown error: exception 'PDOException' with message 'SQLSTATE[...
user avatar
1 vote
1 answer
518 views

Combo Errors MySQL gone away & Product Price :Cannot initialize the indexer process

Indexing Stuck at Processing while Reindexing And The exception.log shows Error as General error: 2006 MySQL server has gone away The Full Exception.log Contents are as follows : 2014-06-17T11:...
user avatar
  • 413
0 votes
5 answers
9k views

Base table or view not found: 1146 Table 'cpaneluser_magento.catalog_category_flat' doesn't exist'

I am getting following error while trying to reindex catalog_category_flat (Category Flat Data). I did tried to repair the database with PHPMyAdmin and magento database repair tool but still not fix. ...
user avatar
  • 169
1 vote
1 answer
252 views

Huge MySql Query processed from frontend

We have several web servers and MySql Server. Once in a while there is a huge query that being requested from one of the web servers (which means it comes from frontend). We have a full page cache ...
user avatar
3 votes
2 answers
12k views

How can I resolve Foreign Key constraint 'SQLSTATE[23000]: Integrity constraint violation:

I have been trying to create a simple module to enable order comments at checkout. I know there are a number of free extensions that do this but I need to add additional functionality so decided to ...
user avatar
  • 2,463
8 votes
1 answer
7k views

How to create an index on a table according to Magento way?

How can I create an index on a existing table by using a script file? Any suggestions will be appreciated. Thanks in advanced.
user avatar
  • 9,857
1 vote
1 answer
287 views

Catalog URL Rewrites Duplicate Index (HY002)

I've seen this problem all around the internet and have yet to find a reasonable solution yet. I've truncated the core_url_rewrite table, set my permissions properly, etc. It seems there is an issue ...
user avatar
  • 861
8 votes
1 answer
614 views

Customized Customer Grid causing MySQL table scans and filesorts aka slow performance

EDIT: I had this question originally posted on Stackoverflow that never got fully answered, I'm posting in here in hopes to shed anymore light on why the massive table scans and any means to prevent ...
user avatar
  • 8,287