The instructions for deploying/creating a https://dev.mysql.com/doc/mysql-operator/en/mysql-operator-innodbcluster-simple-helm.html , You have two options with MySQL, either deploy with A self-signed certificate. This will require setting tls.useSelfSigned An externally signed certificate. his will require providing a tls.caSecretName, tls.serverCertAndPKsecretName and tls.serverCertAndPKsecretName You can set these options like this with --set like this helm install mysql-operator/mysql-innodbcluster \ --set tls.useSelfSigned=true No matter what you'll also always have to provide credentials.root.password for this specific chart --set credentials.root.password=foobar See also, https://devops.stackexchange.com/q/16066/18965

Not the most elegant solution, but I believe we should divide it into two cases, first day or remaining days: with classify (ts, value, init) as -- classify rows as first_day or remaining day ( select ts, value , first_value(date(ts)) over (order by ts) = date(ts) as init from MyTable ), first_day as ( select ts, value, value as valueA from classify where init = 1 ), rem_days as ( select ts, value, sum(value) over (order by ts) as valueA from classify where init = 0 ) select ts, value, valueA from first_day union all select ts, value, valueA + (select max(value) from first_day) from rem_days order by ts ; We don't really need the CTE:s for first_day, rem_days so a slight simplification is: with classify (ts, value, init) as ( select ts, value , first_value(date(ts)) over (order by ts) = date(ts) as init from MyTable ) select ts, value , case when init = 1 then value else sum(value) over (order by ts) - (select sum(value) from classify where init = 1) + (select max(value) from classify where init = 1) end as valueA from classify order by ts Which can be further simplified as: with classify (ts, value, init) as ( select ts, value , first_value(date(ts)) over (order by ts) = date(ts) as init from MyTable ) select ts, value , case when init = 1 then value else sum(value) over (order by ts) -- remove all but last row from offset - (select sum(value)-max(value) from classify where init = 1) end as valueA from classify order by ts ; Yet another way is to partition the cumulative sum, since the case expression only accounts for init <> 1, we will get the running sum for the rest. We then need to add the offset: with classify (ts, value, init) as ( select ts, value , first_value(date(ts)) over (order by ts) = date(ts) as init from MyTable ) select ts, value , case when init = 1 then value -- cumulative sum for init = 0 else sum(value) over (partition by init order by ts) -- add offset + (select max(value) from classify where init = 1) end as valueA from classify order by ts ; https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=9ad89b0a8d8902c9ead5d7658f76c738

No user can alter the information_schema. The schema itself is like a view. So fixing this must be done in the MySQL source code and included in a subsequent release. mysql> alter schema information_schema default character set utf8mb4 ; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' The issue of .NET getting errors related to utf8mb3 has been reported here: https://bugs.mysql.com/bug.php?id=107259 I suggest you should make an account on the MySQL bugs site and click the "Affects Me" button for the above bug. The more people who do that, the more they prioritize working on fixing it. I haven't tried the following solution, because I'm not a .NET user, but based on the release notes, you might be able to fix this in the short term by downgrading back to MySQL 8.0.23 or earlier. This is apparently the result of MySQL gradually migrating to utf8mb4 in stages. First they will change all references to "utf8" to "utf8mb3". Then they will make the old name "utf8" refer to the better implementation utf8mb4. I saw the following statements in the https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ : 8.0.11: The utf8mb3 character set will be replaced by utf8mb4 in a future MySQL version. The utf8 character set is currently an alias for utf8mb3, but will at that point become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8. 8.0.13: The utf8mb3 character set is deprecated and will be removed in a future MySQL version. Please use utf8mb4 instead. 8.0.24: Client applications and test suite plugins now report utf8mb3 rather than utf8 when writing character set names. 8.0.26: These statements now report utf8mb3 rather than utf8 when writing character set names: EXPLAIN, SHOW CREATE PROCEDURE, SHOW CREATE EVENT. Stored program definitions retrieved from the data dictionary now report utf8mb3 rather than utf8 in character set references. This affects any output produced from those definitions, such as SHOW CREATE statements. This error message now reports utf8mb3 rather than utf8 when writing character set names: ER_INVALID_CHARACTER_STRING. 8.0.27: String conversion warnings that previously referred to utf8 now reference utf8mb3 instead. 8.0.29: The server now uses utf8mb3 rather than utf8 in the following cases: In the output of SHOW SQL statements (SHOW CREATE TABLE, SHOW CREATE VIEW, SHOW CREATE DATABASE) When reporting invalid strings. The server now uses utf8mb3 in place of the alias utf8 for character set names when populating data dictionary tables from built-in character sets. This affects the display of character set and related information in the MySQL Information Schema tables listed here: CHARACTER_SETS, COLLATIONS, COLUMNS, COLLATION_CHARACTER_SET_APPLICABILITY, PARAMETERS, ROUTINES, SCHEMATA This change also affects the output of the SQL SHOW CHARACTER SET, SHOW COLLATION, SHOW CREATE DATABASE, and SHOW CREATE TABLE statements. I think the best solution would be for MySQL to finish their migration of all system tables and schemas and variables to utf8mb4.

The MySQL installation instructions on ArtifactHub are incomplete, instead follow the official documentations on https://dev.mysql.com/doc/mysql-operator/en/mysql-operator-installation-helm.html which are currently documented as, helm install my-mysql-operator mysql-operator/mysql-operator \ --namespace mysql-operator --create-namespace You have to specify the namespace with -n or --namespace and if it's not created you'll have to either create it or use --create-namespace

https://dev.mysql.com/doc/refman/8.0/en/char.html says: The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. So there is no need to annotate the value in each row, because it's always exactly the same as the length defined for the column. The length is stored once in the table metadata, no matter how many rows belong to that table. Whereas VARCHAR is stored in a variable-length number of bytes on each row, up to the maximum defined for the column. In C, they might have used null-terminated strings instead of a length prefix. But in SQL, null is a legitimate character to store in a string, so that would not have been a good string terminator.

You must build the JSON path using common string processing. SELECT JSON_UNQUOTE(JSON_EXTRACT(jcol, CONCAT('$[', table2.from, ' to ', table2.to, ']'))) FROM db.table The JSON path is literal and it is not evaluated as an expression, so you must use CONCAT() for the path building. And the expression usage does not allow to use ->> operator, you must use JSON_UNQUOTE(JSON_EXTRACT()) instead.

Try this order: INDEX(emailed, -- for the `WHERE` user_id, id) -- for the `ORDER BY Note: In this context, IS NULL optimizes similar to = constant. So, I put that column first, thereby handing all the WHERE. After that, all the ORDER BY columns. Hence, it would be performed entirely in the Index's BTree. (Cf "covering" or "Using index").

Fastest way if the data comes from a CSV file: LOAD DATA. Next best: "batch" INSERTs -- Load 100 to 1000 rows at a time. (With 9 million rows, that will be thousands of batches, but that is OK.) If the data can be pre-sorted, sort by the PRIMARY KEY and have the PK already included in the table definition. Depending on various factors, it may be faster to drop (or not have) the secondary keys, load the data, then add the secondary keys in a single ALTER. MySQL 5.0 was released in 2005; please consider upgrading. Still, my answer probably applies to all versions of MySQL and MariaDB and to MyISAM and InnoDB.

can someone help me turn this 3 querys into one? SELECT COUNT(*) FROM testes__db WHERE module = %s UNION ALL SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s UNION ALL SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s where eval=0 Then add all parameters... You'll save 2 database roundtrip times which is not much. If you want to overdo it, and "eval" is a bool, you could do: SELECT 0, COUNT(*) FROM testes__db WHERE module = %s UNION ALL SELECT eval, COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s GROUP BY eval So you get the count for both values of "eval" in one query. I gather that it would be faster and better than having the 3 select running all the time. That's not necessarily the case. First, you should benchmark it. Back when I was doing websites, I added a query log at the bottom of the page showing all queries and their execution time. This was displayed only to a user logged in with admin credentials, of course, otherwise it would be a rather large security hole. This is a nice way to immediately spot slow queries while browsing the site. You can do this with a wrapper object on the database connection. Then once you know which queries are actually a problem, you can spend time optimizing them, or doing other things like caching the results.

The short answer is: changing the schema names is probably the best solution. Databases (or schemas as I prefer to call them, because "database" has multiple meanings depending on context) are basically a namespace for tables and other SQL identifiers. Just like any other namespace, if you have two entities of the same name, they can't share a schema. Their names conflict if they're in the same schema. If they have different names, they don't conflict. So if all the tables from one PROD_1 schema are different from the tables of another PROD_1 schema, then you can merge them. But it's not likely that all the tables will be different. You could change the table names so tables from one project have p1_ prefixes, and tables from another project have p2_ prefixes, etc. But you'd have to change all the query code in all of your applications. You could run more than one MySQL instance on your single server. In a way, this creates another level of namespacing, because schemas can have the same name if they are in different MySQL instances. At my last job, we ran MySQL instances in Docker containers with a Docker network so each Docker pod had its own IP address, like a virtual server. Alternatively, you could run multiple MySQL or MariaDB instances on the server without Docker, but you'd have to change the port, socket, and data directory that each one uses. See https://mariadb.com/kb/en/running-multiple-mariadb-server-processes/

If you are using Engine=MyISAM, you should convert to InnoDB. With InnoDB, you probably don't need LOCK TABLES. Why not switch to TEXT (in this case)? For long-running ALTERs, consider using Percona's pt-online-schema-change. Increasing a VARCHAR is faster in new versions of MySQL (including, at least, 8.0) It is faster because it is a "meta" change -- that is, the schema changes, but not the data. Decreasing a VARCHAR must go through all rows and truncate any rows with too much text. As a general rule, if the data has to change, ALTER must copy the table over. Altering the PRIMARY KEY requires restructuring the data's BTree, hence requiring a copy. Many changes to secondary indexes can be done in the background, so they appear to be instantaneous.

To get the column names that start with Name, Val and Var you should use information_schema . Try: select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from information_schema.COLUMNS where COLUMN_NAME like 'Name%' or COLUMN_NAME like 'Val%' or COLUMN_NAME like 'Var%'; Edit There is no table schema, just a single table named 'Table1' with the column names TABLE_SCHEMA represent the database in which you can find all the tables that have the columns which start with Name, Val and Var. If you want only the table names remove from the select statement, select COLUMN_NAME from information_schema.COLUMNS where COLUMN_NAME like 'Name%' or COLUMN_NAME like 'Val%' or COLUMN_NAME like 'Var%'; If you want specific database name use: select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='your_database_name' and COLUMN_NAME like 'Name%' or COLUMN_NAME like 'Val%' or COLUMN_NAME like 'Var%'; If you need specific table name and database name use: select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='your_database_name' and TABLE_NAME ='your_table_name' and COLUMN_NAME like 'Name%' or COLUMN_NAME like 'Val%' or COLUMN_NAME like 'Var%'; Check for more info on https://dev.mysql.com/doc/refman/8.0/en/information-schema.html#:%7E:text=INFORMATION_SCHEMA%20provides%20access%20to%20database,data%20dictionary%20and%20system%20catalog.

That's one month's worth of data? Are you having any trouble with 20K inserts every 15 minutes? (I would expect not.) The table can be shrunk: ad_group_name_id probably does not need to be a 4-byte INT; for 20K groups, SMALLINT UNSIGNED would take only 2 bytes (but overflow at 65K). id (4 bytes) is unnecessary. If you remove it, change the PRIMARY KEY as noted below. DECIMAL(8,2) takes 4 bytes. (6,2) would take 3 bytes (but top out at 9999.99). I recommend this pair of indexes to handle all 3 of your queries: PRIMARY KEY(date_to, ad_group_name_id), INDEX(ad_group_name_id, date_to, cost) A Summary table would work nicely for queries that cover whole days - midnight up to midnight. It would have 1 row per day per group, plus the SUM(cost). A simple aggregation at the end of each day would add the 20K new rows. Then use the Summary table instead of the main table for all 3 of your queries, thereby running upwards of 96 times as fast. (It probably won't be more than 10 times as fast.) It would have two indexes. (Note that day_to would be of type DATE.) PRIMARY KEY(day_to, ad_group_name_id), INDEX(ad_group_name_id, day_to, sum_cost) If you do add the Summary table, you could consider dropping the secondary index from the main table unless it is needed for other lookups based primarily on ad_group_name_id.

Here's the problem you're facing: an SQL query cannot return "dynamic columns." The columns of a query are fixed at the time it is parsed, i.e. before it begins reading any data. The query can't add more columns to its own select-list depending on the data it reads during execution. So you have two choices: Figure out which distinct values you want to become columns, and build an SQL query with those columns. This could be done by running another query first to SELECT DISTINCT Name ... and then using application code to format the pivot query. Some folks use creative solutions with GROUP_CONCAT() to format the query. I'm sure you've seen these solutions. The other strategy is to forget about pivoting in SQL. Just fetch the data as it is in your database, and then write application code to present it in a pivoted format. That's it. Those are your choices. Here's a solution: select '2019' as `Year`, max(case name when 'Name1' then `2019` end) as `Name1`, max(case name when 'Name2' then `2019` end) as `Name2`, max(case name when 'Name3' then `2019` end) as `Name3`, max(case name when 'Name4' then `2019` end) as `Name4` from mytable union select '2020', max(case name when 'Name1' then `2020` end), max(case name when 'Name2' then `2020` end), max(case name when 'Name3' then `2020` end), max(case name when 'Name4' then `2020` end) from mytable union select '2021', max(case name when 'Name1' then `2021` end), max(case name when 'Name2' then `2021` end), max(case name when 'Name3' then `2021` end), max(case name when 'Name4' then `2021` end) from mytable union select '2022', max(case name when 'Name1' then `2022` end), max(case name when 'Name2' then `2022` end), max(case name when 'Name3' then `2022` end), max(case name when 'Name4' then `2022` end) from mytable; Output, tested on MySQL 8.0.29: +------+-------+-------+-------+-------+ | Year | Name1 | Name2 | Name3 | Name4 | +------+-------+-------+-------+-------+ | 2019 | 124 | 102 | 34 | NULL | | 2020 | 98 | NULL | 56 | NULL | | 2021 | 35 | 34 | 97 | 35 | | 2022 | NULL | NULL | 123 | NULL | +------+-------+-------+-------+-------+

Years ago, MySQL supported a way to initialize replication the way you're describing. You configure a replica, and basically tell it to suck the current state of data from the source node. I have never used this feature, and it was quickly removed from the product. I can't even find documentation on this feature now. I think it would have to lock the source table to get the initial state of data, so it would have a pretty high risk that someone could cause an outage by accident just by enabling a new replica. I assume that's why the feature vanished. Recently a plugin called MySQL Clone appears to do the same thing, but it only works with InnoDB tables as far as I can tell. See https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-clone-deployment.html Creating a replica does not need to cause downtime on the source instance. At my last job, we did this frequently to add or repair replicas. We used https://www.percona.com/software/mysql-database/percona-xtrabackup to create a hot backup from the source node, and transferred that backup to the replica server, then started replication using the binary log coordinates recorded with the backup. Once we converted all our clusters to GTID, that got even easier. But I think your task is even easier than this. You don't need to use replication to convert the storage engine of a table. I would use https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html , a free tool that allows you to use ALTER TABLE on a table with virtually no downtime.

The problem was resolved by moving the creation of the table wp_woocommerce_downloadable_product_permissions before the creation of the table wp_wc_download_log

NDB is the only solution you mention that supports horizontal scalability. That is, part of the data resides on each node, so the more nodes you have, the greater the write capacity. The others are all flavors of replication, so every node has its own copy of the full dataset. You could get some amount of read scalability by delegating read queries to the replicas, but only if the read queries are tolerant of replication lag. There is no fully synchronous replication solution. There is always delay between each step: you update a row, then the transaction commits and writes to the binary log, then the binary log is downloaded by the replica, then the replica replays the change against its own copy. If a database could force every step to be synchronous, then the application client would have to wait for everything to complete before the update of a row returns. This would be a deal-breaker for most apps. There's semi-synchronous which you know about, but this does not ensure that writes have been completed on replicas, only that the log has been received by a replica. There's no guarantee of how long it will take the log to be applied on the replica, and lag could even grow to hours under those conditions. I also would not recommend semi-sync over long networks (i.e. inter-regional), because networks can become degraded or interrupted, and that would cause transaction commits on the master node to be blocked. Galera solutions have an option to force read queries to wait for replication lag to catch up. This is not the same a synchronous writes, and it can result in unexpected delays on read queries. InnoDB Cluster monitors replication lag, and if the lag is over 1 second, further writes to the cluster are impeded until the replicas catch up. Another solution that you didn't list is PlanetScale's Vitess and related products. That solution does have horizontal scaling, and manages a lot of the pain points you mention. All horizontally scaled, or "sharded" database (NDB, Vitess) have a necessary downside: because the data is split, there's a big difference between a query that can be resolved by reaching one node versus "fan-out" queries that need to reach many nodes. It's necessary to design your data splitting strategy carefully, so that the queries you need to be most efficient are bound to get their result from one node at a time. This can be quite complex in a relational database, and in some cases there is no clean solution (e.g. many-to-many tables). This means that it's almost never successful to take your incumbent database that was designed with the assumption that all data is co-located, and simply upload it to a sharded database infrastructure and expect it to do well. The only guideline is https://en.wikipedia.org/wiki/There_ain%27t_no_such_thing_as_a_free_lunch . Every solution has its advantages and significant disadvantages. It's up to you to pick a solution whose advantages are important for your project, and whose disadvantages are not a deal-breaker given your needs. You haven't described your workload, like the rate of transactions, ratio of reads to writes, database size, requirements for query latency. Nor have you described how much scalability you need: rate of growth of data and growth of traffic, geographical distribution, number of clients, etc. These are all important factors in choosing a solution. I recommend you either need to try out each of the solutions with a test database to learn how well they match your needs, or you need to hire a database performance consultant to do that work.

There was nothing wrong the with the JSON format. Just a lot of fields descriptions and other information. I used this code to see the where the data actually was $json = file_get_contents('airports.json'); $airportsArray = json_decode($json, true); echo ""; $print_r($airportsArray); echo "";

I recommend storing NULL in the image_count column on rows where it is inapplicable because the type doesn't need it. NULL is different from an integer 0. InnoDB doesn't store a value in the row for a NULL. So it's pretty low overhead to leave the column NULL on the rows that don't need it. There's a bit stored in the row header for each nullable column, but that will be needed whether you store a value or not. 10k rows is small for an InnoDB table. I wouldn't worry about it unless you need to run this database on a Raspberry Pi or similarly compact platform. SQL does not have any syntax for SELECT * except-the-columns-I-don't-want. If you use SELECT *, that means all the columns, including those that are NULL. If you want a subset of columns, you have to name all of the ones you want.

I tested your query and I found that when it uses the alert_raisedDateEventId index, there is no Using filesort in the EXPLAIN report. That's why it's using that index, so it can read the rows from the index in the same order that you want them sorted, therefore the ORDER BY is a no-op. But this means the first row of the EXPLAIN shows it's doing type: index which means an index-scan. It has to read every entry in that index, even though after it does all the joins, many of them might be eliminated. The other joins are all type: eq_ref which means joining to the respective tables' primary keys. This is an efficient way to do the joins, and the optimizer prefers it. So the combination of no filesort and joins by primary key makes the optimizer happy. Try this query: explain select `event`.`id` as `eventId` from `landlord` inner join `property` on `landlord`.`id` = `property`.`landlordId` inner join `event` on `property`.`id` = `event`.`propertyId` inner join `alert` on `event`.`id` = `alert`.`eventId` where `landlord`.`reference` in ( 'landlord1', 'landlord2', 'landlord3', 'landlord4', 'landlord5', 'landlord6', 'landlord7', 'landlord8' ) order by `alert`.`raiseddate` desc limit 20 landlord should use its reference index to make that condition be a range lookup, to select a subset of the rows in landlord, and then the subsequent tables are joined to that result. The joins will be by non-primary indexes, but that might be okay. But it will also force alert to be the last table joined, and you can't get rid of the filesort in that case. You can only optimize the filesort away if the order is based on an indexed column of the first table in the series of joins. This might not be a problem if the result set after all the joins have been applied is pretty small. You might have to use from landlord force index(reference) if the optimizer tries to reorder the tables back to the original plan.