In my recent post about the Left-Prefix Index Rule, I explained how queries can use all or part of a compound (multi-column) index. Knowing what makes an index fully usable by a query is important, but it's also important to know how to figure out how much of an index a query is able to use. In this article I'll show you how to do this by reading the query's explain plan. This article covers MySQL, Postgres, and MongoDB.

As a quick review, a query can use an index if it has filtering values that constrain a contiguous leading portion of the index, up to and including the first inequality condition in index-column order. Now let's see where the database server exposes how much of the index is used.

MySQL

In MySQL's …

ProxySQL 1.4.14, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL,  and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 1.4.14 source and binary packages available from the  …

This event was just awesome. We got 110 participants ! Thank you everybody and also a big thank to the speakers.

Here are the slides of all the sessions:

• MySQL Shell – A DevOps-engineer day with MySQL’s development and administration tool, Miguel Araújo
• MySQL Shell : the best DBA tool ? – How to use the MySQL Shell as a framework for DBAs, Frédéric Descamps
• Tuning MySQL 8.0 InnoDB for High Load, Dimitri Kravtchuk
• …

In my webinar How To Rock with MyRocks I briefly mentioned the column families feature in MyRocks, that allows a fine tuning for indexes and primary keys.

Let’s review it in more detail.

To recap, MyRocks is based on the RocksDB library, which stores all data in [key => value] pairs, so when it translates to MySQL, all Primary Keys (data) and secondary keys (indexes) are stored in [ key => value ] pairs, which by default are assigned to “default” Column Family.

Each column family has individual set of

• SST files, and their parameters
• Memtable and its parameters
• Bloom filters, and their parameters
• Compression settings

There is a N:1 relation between tables and indexes to column family, so schematically it looks like this:

How do you assign …

Last week I presented the following session at the pre-FOSDEM MySQL Day:

MySQL Shell : the best DBA tool ? from Frederic Descamps

The audience seemed very interested on how the MySQL Shell can be extended.

During the presentation I showed how I extended the MySQL Shell with two new modules in Python:

• mydba
• innotop

Both projects are on github and are waiting for ideas, feature requests, pull requests, …

Here is …

The next two weeks are a big change for me compared to the last two weeks.  The last two weeks I was in Belgium and now I am in Miami before heading to the Dominican Republic.  The one thing in common is that I am speaking on the MySQL Document Store.

SunshinePHP has become a favorite conference for many PHP developers for many reasons and this morning I will be running a three hour tutorial on using the PECL MySQL X DevAPI and the MySQL Document Store with PHP and then have a one hour talk later in the show. Plus I am joined by the amazing Kathy Forte who will speak on Driving Innovation With MySQL 8.0 and Docker.

Pycaribbean is a new show hoping to foster a growing developer community into the area.  And i will be showing how to use the MySQL Document Store with Python.All opinions …

Tweet

MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.

The statement to load the data is LOAD DATA INFILE. By default the file must be server-side and MySQL …

In this post, we’ll cover Percona’s thoughts about the current MySQL community discussion happening around MySQL LOCAL INFILE security issues.

This post is released given the already public discussion of this particular issue, with the exploitation code currently redacted to ensure forks of MySQL client libraries have sufficient time to implement their response strategies.

This post has been updated to now include previously redacted content, in line with responsible disclosure sufficient time has passed to allow forks to update and get those updates out for circulation.

Background

MySQL’s

LOCAL INFILE

  feature is fully documented by Oracle MySQL, and there is a legitimate use for the

LOCAL INFILE

 feature to upload data to a …

What a great place for informal interactions, strengthening the network, and hearing the latest news from the grapevine! Last weekend 1.-3. Feb 2019, over 8000 developers met in Brussels for FOSDEM 2019. For the overall atmosphere, take a look at this 1:05 long video by Sofia Ek. MariaDB Foundation was present with six staff people […]

The post FOSDEM Reflections / MySQL – MariaDB DevRoom appeared first on MariaDB.org.