Page 9 - MySQL

MySQL ships a built-in mechanism called the Event Scheduler for running recurring SQL statements directly inside the database server, with no external cron daemon involved. This article walks through the CREATE EVENT syntax and the available scheduling options, draws a clear line between the Event Scheduler and Magento's own cron system plus OS-level cron, and explains how to monitor failed events before they stay silently disabled.

The MySQL optimizer decides on a query's execution plan based on estimated row counts, and for a long time that estimate relied almost entirely on coarse index statistics like cardinality values. For evenly distributed columns that works fine, but for skewed distributions with a few very frequent and many rare values, the same estimation method regularly produces wrong plans. Since MySQL 8, histograms can be maintained on individual columns, giving the optimizer a much finer grained data foundation. This article covers when classic estimates fail, how histograms are used in practice, and when they actually make a measurable difference.

The four isolation levels in MySQL sound similar in the documentation, yet they behave fundamentally differently under load. This article explains READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE concretely in InnoDB, with a particular focus on the default isolation level REPEATABLE READ and its snapshot-based consistent-read behaviour.

The native JSON type lets you store semi-structured data directly in MySQL and query it efficiently with JSON_EXTRACT and JSON_TABLE. Without generated columns for indexing, however, JSON queries stay slow, and without clear criteria for its use, JSON quickly turns into an excuse for missing schema design.

Anyone migrating an application between PostgreSQL and MySQL, or running both systems side by side, quickly runs into syntax differences that look harmless at first glance but can delay an entire migration. This article compares data types, auto increment, JSON support, pagination, and upsert syntax between PostgreSQL and MySQL using runnable examples.

Anyone processing customer data in a Magento store must be able to prove, if it ever comes to that, who accessed which data and when. That is exactly what audit plugins are for: they log connections, queries, and administrative changes at the database level, regardless of whether access happened through the Magento application, a reporting tool, or straight from the command line. This article shows how MySQL Enterprise Audit and the free alternative, the Percona Audit Log Plugin, work, how to avoid log flood with targeted filters, and which GDPR-relevant use cases stand behind it.

Anyone running InnoDB without a deliberate tablespace strategy accumulates an untidy system tablespace over the years that never shrinks again. innodb_file_per_table, OPTIMIZE TABLE and general tablespaces hand back the control over disk space, backup granularity and maintenance windows that gets lost otherwise in standard operation.

Seed data is fundamentally different from plain init scripts: init scripts create the schema, seed data fills it with realistic, repeatable test data. Anyone mixing up both concepts ends up either with empty development databases or with inconsistent test states between team members.

Claude can turn a plain-language description of a schema change into a first draft of a db_schema.xml diff or a data patch, speeding up the early stages of a Magento database migration considerably. Still, no AI suggestion replaces manual review, since changes to production data are rarely reversible without consequences and deserve extra care at every single step.

Not every search field needs Elasticsearch right away. With FULLTEXT indexes, natural language mode, and boolean mode, MySQL ships a genuine full-text search directly in the database, sufficient for many applications, without extra infrastructure, extra latency, and extra consistency problems between two systems.