33

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 ...


23

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. ...


16

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 ...


12

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.


11

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

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) ...


9

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 ...


9

The memcached plugin never did skip the overhead of InnoDB. It was an API, not a storage engine. Your data posted to the memcached plugin was still subject to transactions, still written to InnoDB's redo log, still cached in the buffer pool, etc. You could even access the same table via SQL. The only advantage to the memcached plugin was that it skipped SQL ...


8

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 ...


7

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.


7

Hope you're doing great. I would like to see your schema to make sure all is good. I tried to recreate what I believe you did. Here is what I did: mysql> create database test295895; mysql> use test295895; mysql> create table my_table (my_column JSON); Then I inserted the values you showed: mysql> insert into my_table VALUES('{"my_value"...


6

(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 ...


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

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 ...


6

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....


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. ...


6

The event_Scheduler is, as it's name suggests, a way to Schedule Events (in this case queries) to run in MySQL at a given time. It is simply waiting for an event to trigger it and tell it to do something. As you can see you have no events set up, so it will be waiting for a long time. It is enabled by default, but can be disabled by running: SET @@global....


6

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 ...


6

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 ...


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

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


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

This worked for me: Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore. Then sudo service mysqld start


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 ...


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

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.


4

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 ...


4

In the security section of the documentation it states: If you do not know the initial random password, look in the server error log. Try running the following command. It works for me on my 5.7 installation (which has the same instructions as the 8.0 version of the documentation): sudo grep 'temporary password' /var/log/mysqld.log It should tell you ...


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