I have something similar running in our environment. Here is how we did it. You only have to worry about what you have sent from your SQL Server database. Create an SSIS package to load data into MYSQL, BUT you are going to be using a filter from a table that you create. The primary key is the last record sent. The table should look like this. Tablename | Key Customer 101 Accounts 521 At the last step you need to insert into this table the max Primary key ID for the table you sent. Then the next time the time the package run just filter the data being sent by the table. Example Select * FROM Customer where PK_ID > 101. Just ensure your package has the correct error handling. Hopefully this makes sense.

I think you're having more of a collation issue as opposed to a character set issue. Do you know what the collation was in SQLite and what it now is in your MariaDB instance? You can https://mariadb.com/kb/en/setting-character-sets-and-collations/ to something more appropriate like the default of latin1_swedish_ci which I believe would then allow the insert of the second row (treating those two values as different values). Collations can be specified at the server, database, table, and column level. If you want to change it at the database level, which is typically most appropriate (i.e. you should generally use the same collation throughout the entire database) you can accomplish that with the https://mariadb.com/kb/en/alter-database/ command. For example: ALTER DATABASE YourDatabaseName CHARACTER SET='latin1' COLLATE='latin1_swedish_ci'; For reference, here's https://mariadb.com/kb/en/supported-character-sets-and-collations/ MariaDB supports too, with latin1 being the default.

As you wrote "but I'm curious if there's a specific solution for other flavours of SQL" This would be possible in Postgres using an https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION alter table tires add constraint single_tire exclude using gist (car_id with =, tire_id with <>); So the first combination of (car_id, tire_id) will then constrain all subsequent ones. If there is a row with (1,1,x), inserting another row with (1,1,y) will work, but inserting (1,2,y) will not.

I (seem to have) fixed this by adding a query to check the creation date of the account. and created_at >= (select created_at from customers where username = '__username__' ) (username is string_replaced in php with the actual username. I did try username = c.username first but that didn't acheive the performance improvement so I resorted to the php string replace (Note, this is not user-supplied info, so not a sql injection risk) select created_at from transactions where created_at < date_add('2022-02-27 06:00:00',interval -60 day) /* Added this line... */ and created_at >= (select created_at from customers where username = 'username' ) and customer_username = 'username' and status = 'SUCCESS' and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE' order by created_at desc limit 1 It was performing very slow for accounts that hadn't actually been created in the date range being queried, so the very slow query was coming back NULL

I got it to work using not exists and a subquery. SELECT COUNT(pr.id) AS product_count FROM products pr WHERE NOT EXISTS( SELECT * FROM vendor_product_map vp WHERE vp.product = pr.id AND vp.vendor = 3 )

I would consider separate tables. Think about all of the data analysis you may ever need to do in the future, and plan a schema that supports that. With single counts in the post_stats table, you'll only ever know a post was viewed x time per day, or y times overall, etc.. Instead, consider dropping that table all together, and creating a tables called Views and Likes. For example. the Views table could be foreign keyed to the posts table. In this table you could store details such as... view_id post_id viewed_by datetime_viewed With this schema, you could query total views by post or by user, and for any time period you choose. For example... views by post for today views by user for the past week views of user's posts for the past week etc.. You could then create various views to return stats from those tables, as you see the need. Going with this strategy, you'll will not be limited on what you can analyze later. Additionally, it would would just be a simple insert anytime an action happens, vs an update that may conflict with another simultaneous update. The same concept would apply for Like, etc..

Let's look at this list of overheads: Overhead for a statement Overhead for a row Overhead for a function Those are in order starting with the most overhead. That is, the function overhead is insignificant in the big picture; don't worry about it. See also COALESCE(). Caching With Engine=InnoDB, the main cache is the "buffer_pool"; innodb_buffer_pool_size should be about 70% of available RAM. All actions on rows happen in the buffer_pool: Read block(s) from disk (if not already in cache) Find the row(s) in the block(s) Fetch (SELECT) or modify row(s) Eventually write the block back to disk (if modified) Since you are asking about SELECT, only steps 1,2,3 are relevant. Furthermore, if you keep Selecting the same row (or nearby rows), step 1 is not repeated. Note: This does not mention any impact of function calls. Note: On a cold system (nothing in the cache), the first SELECT will be slower due to step 1. After that the same or 'similar' queries will be faster (sometimes 10x faster). Comment: ON t1.id = t2.id Do you have two tables with identical PRIMARY KEYS? Why? The main way to have good performance in a JOIN is to have suitable indexes. Let's see the real query and SHOW CREATE TABLE for the tables being Joined. The BNL mentioned in a comment is an internal "caching" to speed up certain Joins. You have little control over its use. It refers to fetching all the stuff needed from a secondary table and storing it in an in-memory hash. It only applies when Joining. Function calls are not involved (in your example). If a function (eg, IFNULL) were used in ON or WHERE, it is likely to prevent the use of an otherwise suitable Index. (cf not "sargable")

INSERT INTO fruit (fruit_name) VALUES ('apple'), ('banana'); The list can be as long as needed. There are limits in the megabytes. A thousand rows is usually workable.

MyISAM may the main problem. Any write (including replication writes) to a table blocks all other writes and, to some extent, even reads. Hence, the "parallel" replication threads are sometimes blocking each other. And even SELECTs on the Replica are blocked and/or block replication. I strongly encourage you to switch to InnoDB, then come back if you still have issues. InnoDB transactions and replication are better kept in order even without completely stalling other replication threads.

mysqlrouter should be installed in the application that is going to connect to innodbcluster

with CustomerRank as ( select a.province ,c.name ,dense_rank() over(partition by a.province order by c.birth_date desc) as AgeRank from customer c join address a on c.cust_id = a.cust_id ) select * from CustomerRank a where a.AgeRank = 1; Example fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=016888fbaf40093edfdf7ad22412c215 The idea is to create a list on which you can then pick the answer you need. In this case, generating a ranking of birth_date per province (AgeRank) then picking all the rows where AgeRank is 1 (i.e. the youngest as we've order by birth_date desc).

You can increase max_connections without increasing RAM. BUT--Let's discuss whether 175 might actually be too big. If Max_used_connections has not reached 175, then max_connections is not the real problem. If you have hit that limit, then let's start by investigating what the clients are. If connections come mostly from a web server, then it is probably configured too high. If it is coming from applications, are they failing to close their connections? Is there some form of "connection pooling"? If so, what limits does it have? Turn off the query_cache; it is (usually) more burden than benefit. Set these below 1% of RAM: tmp_table_size, max_heap_table_size. They are not just per query, but possibly per subquery. Anyway, 1G is very much into "diminishing returns". Disk-based temp tables happen for a variety of reasons; changing those two settings cannot get rid of all disk temp tables. Based on the setting of innodb_buffer_pool_size, I suggest that a lot of RAM is going unused. I agree that 0.21% is quite low. If you would like some more metrics like that, see http://mysql.rjweb.org/doc.php/mysql_analysis#tuning . It will also provide metrics saying whether the Query cache is being useful. "high load scenario" -- What does that mean? If it is "high 'Load Average'", that that is equivalent to "high CPU". This issue is best tackled by looking for the slowest and/or most common queries and trying to speed them up. Use the http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog to help with that.

wait_timeoout is rather cryptic. There are about 4 values in 2 dimensions: GLOBAL vs SESSION: you probably dealt with that by restarting. The GLOBAL value is used to initialize the SESSION value when opening a connection. That is, SET GLOBAL ... has no impact on most SESSION variables of currently running connections. batch versus interactive. interactive_timeout is used when using the mysql commandline tool. wait_timeout is for apps (PHP, Java, etc). Traditionally, SET GLOBAL sticks only until the next restart. However, with MySQL 8.0, there is a PERSIST option to make it stick. To see the timeouts: SHOW GLOBAL VARIABLES LIKE '%timeout'; SHOW SESSIOn VARIABLES LIKE '%timeout'; If you are disconnected and reconnected due to wait_timeout, the STATUS value Aborted_clients will be incremented by 1.

Can I use the operators(AND & OR) like this in a LEFT JOIN after ON? Yes, you can use AND and OR operators within your JOIN clause. Is this query correct and most efficient way of achieving this? mysql. The below conditions are going to cause you performance issues with your query. lower(t2.creditor_name) LIKE '%vehicle finance%' OR lower(t2.creditor_name) LIKE '%vehicle and asset finance%' Anytime you wrap a function around a column, such as lower(t2.creditor_name), you are forcing the query engine to convert that column to lowercase for every row in the table, before it can compare it to something. Putting a leading % in a LIKE condition is the equivalent of looking for '%oe, John' in the phonebook instead of 'Doe, Joh%'. Without knowing the first letter is D, someone would have to scan every page of the phonebook to find a record that could be a possible match. Using these anti-patterns is generally a bad idea, and most often will make it very difficult for the query to utilize an index. You might not notice the impact initially, if your table only has a few hundred rows in it. However, as the data set grows, and you start getting into the 100s of thousands or millions of rows, it will most certainly matter.

How to run multiple instances of MySQL Server on one server is in the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/multiple-servers.html

Add INDEX(groupID, timestamp) It will help some of the cases you presented. Are your time ranges always full days? If so, daily subtotals in a summary table would be much faster. Please provide SHOW CREATE TABLE. With that many rows, let's discuss the datatypes. And if you have an id whether you can get rid of it -- and promote one of the indexes to be the PK. How long does the insert of 5K rows take? If needed, we can discuss improvements on that. Since you have a 2-dimensional index, it may be worth discussing PARTITIONing. But first, will you be deleting "old" rows? If yes, then I will definitely push for PARTITION BY RANGE TO_ROWS(..). How much data will be kept (eg, "one year's worth")? How many different "groups"? How much RAM? What is the setting of innodb_buffer_pool_size?

What is your client language? The API to MySQL will have a function with a name something like LAST_INSERT_ID() That is used after doing the first INSERT to get the Auto_increment id from it so you can use it in the next Insert (or an Update).

So, long story short. There is a bug in xtrabackup that can be hit when meeting certain conditions on the database size/layout/qps; for those who meet it it's 100% reproducible (on the other hand this version/certain binaries can successfully work with other instances). In the same time I managed to find a thread on a chinese server with half-chinese/half-english discussion describing the root cause of this (no traces in the english part of the world though). This bug has nothing to do with undo tablespaces metrics that can be seen. In fact, despite the zeroes, these tablespaces are fully functional. I was unable to reproduce the issue with xtrabackup 8.0.26.

SELECT SUM(col1 IS NOT NULL), -- if "empty" means NULL SUM(col2 != ''), -- if "empty" means empty-string ... FROM tbl; A non-zero result means that the column is 'not empty'.

The advice you quoted from the documentation is old, and probably only applies to MyISAM tables. For InnoDB tables, you can make the query skip reading long datatypes (TEXT/BLOB/VARCHAR/JSON) by just omitting them from your select-list. That is, don't use SELECT *, but instead select only the columns you want to read, by name. InnoDB will skip reading extra pages for long columns that are omitted from the select-list. That will probably be a sufficient optimization for you, and does not require you to split the table. Admittedly, InnoDB may store short strings on the same page with the rest of the row, if they fit. That is, if you have a JSON column, but on a given row it happens to be short enough to fit in the same page with the other columns for the respective row, then InnoDB stores them together. So the scenario does exist in which one might need to separate the JSON column to its own table, to get that last 0.0001% optimization. But you haven't described that you are operating at the scale that would require this. You are optimizing prematurely. This is pretty much by definition, if you haven't actually measured performance to show that you have a problem related to storing the columns together, and that the alternative design fixes that problem. There's a reason that Computer Science is a scientific field. You should think like a scientist, and make an experiment to measure performance with both table designs. Then you'll know that you aren't optimizing prematurely.