Home News Feeds Planet MySQL
Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • How to Install and Use MySQL on Ubuntu 20.04
    In this tutorial, we are going to show you how to install MySQL on your Ubuntu 20.04 and how to ... Read more The post How to Install and Use MySQL on Ubuntu 20.04 appeared first on RoseHosting.

  • OpenLamp.tech issue #4
    Over the weekend I’ve published another issue of OpenLamp.tech, the newsletter for PHP/MySQL developers. There is plenty of great curated content for you so dive right in and enjoy! If you’re a PHP/MySQL developer, this free weekly newsletter is for you. I’ve curated some great reads for you this week from around the web. Stories you can enjoy in this issue are: Distance querying with MySQL geospatial functions in LaravelCursors and for loops in MySQL stored proceduresGreat YouTube channel/video recommendations for learning PHPDon’t wait on me each week to repost here. Sign up and have each issue delivered directly to your inbox so you don’t miss any. The monthly deep-dive featured issue releases on the last Friday of this month/year and I’m covering MySQL Database Metadata with CodeIgniter 4. (Related: Last month’s deep-dive featured piece, 5 MySQL String Functions You Should Know, was a jam-packed issue with tons of great content so do check it out!) Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! Support my work here on this blog by tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #4 appeared first on Digital Owl's Prose.

  • InnoDB vs MyISAM: A Detailed Comparison of Two MySQL Storage Engines
    If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you are about to choose one of them, we believe you would like to find out what each of them […] The post InnoDB vs MyISAM: A Detailed Comparison of Two MySQL Storage Engines appeared first on Devart Blog.

  • Connecteam - our Vitess story
    Connecteam is a SaaS company that provides an employee management solution for deskless teams. Over the last couple of years, we’ve been growing tremendously and we recently started to face one of the hardest technical challenges: horizontal scaling. In the era of cloud computing, provisioning new resources is a breeze, but handling those resources in an efficient manner and providing a five nines uptime while allowing a fast-paced development environment is not an easy undertaking.

  • MySQL 8.0 Document Store – How to deal with date & time
    As you know, MySQL 8.0 can be used as JSON Document Store to store your documents without being linked to any schema. You can also use CRUD operations to deal with these documents using the MySQL X DevAPI. Of course in documents, it’s possible to also store temporal attributes like date, time, datetime, … Let’s see how we can deal with such attributes. This is an example of a collection of documents with a datetime attribute createdOn: As those attributes don’t have a real type, can we use the createdOn attribute as if it was a real datetime field ? Let’s try to get all the documents have a created data > '2021-12-02': We can see that the document “dave” has been filtered out. However, we can see that “kenny” and “miguel” are also present… and this is correct as “2021-12-02 01:20:19” is indeed bigger than “2021-12-02 00:00:00“. Let’s then try with another date format : 2021-12-02 23:59:59: This is indeed what we are looking for… but can I just format the value of createdOn to just filter out using a date without the time ? You can notice that we can use datetime functions on the attribute. However not all functions are always supported in the X DevAPI: And what about performance ? Let’s run again this operation: JS> db.mycol.find("createdOn > '2021-12-02'").fields('name', 'createdOn') We can find in Performance_Schema the statement as it’s executed by MySQL and check its Query Execution Plan (using EXPLAIN): SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (JSON_EXTRACT(doc,'$.createdOn') > '2021-12-02') \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100 Extra: Using where We can notice that a full table scan is performed to execute the query. But of course, it’s again possible to optimize this by creating an index: Now if we check again the Query Execution Plan of the query, we can see that the index is used: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_idx key: createdOn_idx key_len: 6 ref: NULL rows: 3 filtered: 100 Extra: Using where But sometimes, the MySQL DBA still needs to use the good all SQL: let’s now decide that we want to use this statement and that we don’t want to change it: JS> db.mycol.find("date(createdOn) > '2021-12-02'").fields('name', 'createdOn') Could we optimize it ? Because this CRUD operation, won’t use the previously created index (due to the date()function on the attribute). That’s where the MySQL DBA will prove again all the power she/he has ! The trick here is to create a virtual column manually and then index it: SQL> ALTER TABLE mycol ADD COLUMN created_on_date date GENERATED ALWAYS AS ( date(json_unquote(json_extract(doc,_utf8mb4'$.createdOn'))) ) VIRTUAL; SQL> ALTER TABLE mycol ADD INDEX createdOn_date_idx(created_on_date); And now the index will be used: SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (date(JSON_UNQUOTE(JSON_EXTRACT(doc,'$.createdOn'))) > '2021-12-02')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_date_idx key: createdOn_date_idx key_len: 4 ref: NULL rows: 1 filtered: 100 Extra: Using where As you can see, it’s easy to use Date & Time data inside JSON documents using the X Dev API with MySQL 8.0 Document Store. If needed, it’s also possible to ask the MySQL DBA to generate functional indexes on virtual columns to speed up some operations. As you may know, MySQL X Protocol is also available in MySQL Database Service on OCI. Enjoy MySQL !

Banner
Copyright © 2021 zekersplayground.com. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.