32

Instead of '2020-04-30' = date_add(op.dataDate, INTERVAL 14 DAY); Use op.dataDate = date_sub('2020-04-30', INTERVAL 14 DAY); Your first statement will be interpreted as "add 14 days to all dataDate and return when that is 2020-04-30." This will require a full scan of the table. The second statement will evaluate to: "return records where ...


22

According to Dave Stockes, a MySQL Community Manager for Oracle: (from his blog post: MySQL 8 is coming) Years ago, before the Sun Microsystems purchase of MySQL AB, there was a version of MySQL with the number 6. Sadly, it was a bit ambitious and the change of ownership left it to wither. The MySQL Cluster product has been using the 7 series for years. ...


15

There is a detailed blog from the MySQL server team about this, where Matt Lord says: The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines. We considered what improvements we could make to query cache versus optimizations that we could make which provide ...


10

Good riddance !!! It is a challenge for most database developers to correctly estimate the size of the most common result sets in their applications. Having a large query cache was just a big bandage for that. There is a bigger reason that foreshadowed the demise of the query cache: Four years ago (June 07, 2014), I answered the post Why query_cache_type ...


10

Maybe this information is useful. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html Note This release includes a change to the innodb_index_stats and innodb_table_stats system tables. When upgrading to this release, be sure to run mysql_upgrade in order to include these changes.


8

The server variable lower_case_table_names is described in the relevant documentation page: Identifier Case Sensitivity, where it also mentions: lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited. Some more details appear in the linked page ...


7

MySQL 8.0+ MySQL supports Point(x,y) which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point with ST_SRID(srid) SELECT ST_SRID( Point(0,0), 4326); This is a relatively new feature implemented in MySQL 8.0 with #WL8543. MariaDB does not support it. In PostGIS, you would use, SELECT ...


6

In addition to specifying the corresponding command line argument, you can also add an option to my.cnf: Edit my.cnf and add skip-log-bin in the [mysqld] section. Restart mysqld Enjoy your lack of binlogs Note that this will remain in effect until you remove the option from the configuration file.


6

The issue is a Mysql bug (https://bugs.mysql.com/bug.php?id=94185 ) Anyway, some tuning mitigated the issue and stopped the number of open files to grow. Specifically, we set: innodb_lru_scan_depth=100 innodb_flushing_avg_loops=5 thread_cache_size=216 Fixed as of the upcoming 8.0.16 release, and here's the changelog entry: Static thread local variables ...


6

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this way it can use up to all the amount of available memory. Did the trick for me. Unfortunately, I wasn't the guy to figure it out, but rather guys from paid ...


6

The formulation is the main problem. After rewriting the query, the index you have can be used for the entire WHERE clause. SELECT * FROM op.prices WHERE ticker = 'AAPL' AND expDate >= '2020-04-30' AND expDate < '2020-04-30' + INTERVAL 3 MONTH AND dataDate = '2020-04-30' - INTERVAL 14 DAY See Sargeable in Wikipedia. ...


5

(I agree with the other Answer, but here is my 2-cents-worth.) As implemented, ... The QC cannot work with Galera or Group Replication, both of which are getting more traction in the HA arena. When query_cache_size got big, it got less efficient. This is due to inefficiencies in "pruning". (Note: Aurora reimplemented it, and seems to have fixed this ...


5

It's a known bug, fixed in 8.0.12. REGEXP_RELACE() results from one result set row could carry forward to the next row, resulting in accumulation of previous results in the current row.


5

I found your question after looking into why I often had poor performance with inserts and updates, and with connection frequently in the state "waiting for handler commit". What I have subsequently found is that in MySQL 8.0 binary logging is enabled by default. That is a significant change from MySQL 5. I'm not using replication so didn't need binary ...


5

You're looking for GROUP_CONCAT: SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list FROM Rider GROUP BY weight


5

That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability. When you use Geometry on MySQL (any SRID) except 4326 you use (long,lat). That's (x,y) on a Cartesian system. When you use Geography on MySQL (SRID = 4326) you use (lat, long). That's (y,x) ...


5

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.


5

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII ...


5

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms. Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing. In terms of speed you use INTEGER, and consider other like varchar(36) for uuids when ...


5

If there is one email per user, add a column to the users table. In general 1:1 mappings are not a good idea between tables; simply combine the tables. UUIDs are a bad idea (for multiple reasons) unless you must generate unique identifiers in some distributed way. A VARCHAR is not necessarily 'bad'; it may even save space over adding an otherwise unnecessary ...


4

I was finally successful restoring the data. My problem was, that I tried to restore the data with a docker-compose, which somehow didn't work. So I took a different approach 1. Run a docker image of mysql:8 and mounted the "corrupt" folder to it docker run -d -e MYSQL_ROOT_PASSWORD=root -v /path/to/corrupt/folder:/var/lib/mysql --name mrestore mysql:8 ...


4

Review of GLOBAL STATUS and VARIABLES Observations: Version: 8.0.13 240 GB of RAM Uptime = 10:47:09; some GLOBAL STATUS values may not be meaningful yet. You are not running on Windows. Running 64-bit version You appear to be running entirely (or mostly) InnoDB. The More Important Issues: Even though table_open_cache is high, it may be good to raise it ...


4

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to be something controlled by one of MySQL Workbench settings. Specifically, Valeriy Kravchuk suggested this resolution: Please, check "Show Metadata Schemata" ...


4

Shane Bester made a suggestion as a comment on my bug report which explains that this is a known bug and will be resolved in next release. More importantly, there is a workaround which I've tried with great success. Thanks Shane wherever you are! internal_tmp_mem_storage_engine=MEMORY Shane said this: Thanks for the test data. Found a workaround on 8....


4

To generating random password for user while creating new one you can use : mysql> CREATE USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD; +----------+-----------+----------------------+ | user | host | generated password | +----------+-----------+----------------------+ | ran_user | localhost | UN:5_z05J._*VtU3K%Qu | +----------+-------...


4

Actually, my answer is in the Documentation: https://mariadb.com/kb/en/unicode/ UTF-8 encoding using one to three bytes per character. Basic Latin letters, numbers and punctuation use one byte. European and Middle East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese ideographs use 3-bytes. No supplementary characters are stored. utf8mb4 -- ...


3

Do you have any clue what is going on when the Warning is being generated? It sounds like some form of version mismatch. Do you have any Plugins? Please file a bug at bugs.mysql.com . Meanwhile, do USE mysql; SHOW CREATE TABLE innodb_index_stats ; Check, in particular, the definition of table_name. It used to be `table_name` varchar(64) COLLATE ...


3

In command line: Run mysql_upgrade --user=your_user_name --password Enter password. You may have to use --force if the database have already been upgraded and something went wrong (which is probably the case if you are here). Don't worry, mysql_upgrade will tell you about this and as a result you just have to re-run it with --force. That's it.


3

The short answer is: this is a feature. The CREATE REPLICATION FILTER syntax was created as a way to dynamically modify replication filters on the slave without a restart. (bug report) You are right, that the documentation is a bit lean on this, but I found this snippet from FAQs in the MySQL Release announcement of the feature in 5.7: 11) Are these ...


3

The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions. In general, partitions do not on their own improve performance, but there are a some scenarios where they can help: Secondary indexes are local to the partition, so if you ...


Only top voted, non community-wiki answers of a minimum length are eligible