JSON

Creating Geo-Enabled applications with MongoDB, GeoJSON and MySQL

Creating Geo-Enabled applications with MongoDB, GeoJSON and MySQL

This blog post will discuss creating geo-enabled applications with MongoDB, GeoJSON and MySQL.
Introduction
Recently I published a blog post about the new GIS features in MySQL 5.7. Today I’ve looked into how to use MongoDB (I’ve tested with 3.0 and 3.2, with 3.2 being much faster) for the same purpose. I will also talk about GIS […]

Read more

EXPLAIN FORMAT=JSON wrap-up

EXPLAIN FORMAT=JSON

This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.
In this series, we’ve discussed everything unique to
EXPLAIN FORMAT=JSON. I intentionally skipped a description of members such as
table_name,
access_type  or
select_id, which are not unique.
In this series, I only mentioned in passing members that […]

Read more

JSON document fast lookup with MySQL 5.7

In this blog post, we’ll discuss JSON document fast lookup with MySQL 5.7.
Recently I attended Morgan Tocker’s talk on MySQL 5.7 and JSON at FOSDEM, and I found it awesome.
I learned some great information from the talk. Let me share one of them here: a very useful trick if you plan to store JSON documents […]

Read more

EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent

EXPLAIN FORMAT=JSON

Once again it’s time for another EXPLAIN FORMAT=JSON is cool! post. This post will discuss how EXPLAIN FORMAT=JSON allows the nested_loop command to make the JOIN operation hierarchy transparent.
The regular
EXPLAIN  command lists each table that participates in a 
JOIN  operation on a single row. This works perfectly for simple queries:

MySQL

mysql> explain select * from […]

Read more

EXPLAIN FORMAT=JSON: cost_info knows why optimizer prefers one index to another

Percona Server 5.7 performance improvements

Time for another entry in the EXPLAIN FORMAT=JSON is cool! series of blog posts. This time we’ll discuss how using EXPLAIN FORMAT=JSON allows you to see that
cost_info  knows why the optimizer prefers one index to another.
Tables often have more than one index. Any of these indexes can be used to resolve query. The optimizer has […]

Read more

EXPLAIN FORMAT=JSON: buffer_result is not hidden!

MongoDB System Tuning

Time for another entry in the EXPLAIN FORMAT=JSON is cool! series. Today we’re going to look at how you can view the buffer result using JSON (instead of the regular
EXPLAIN command.
Regular
EXPLAIN does not identify if
SQL_BUFFER_RESULT was used at all. To demonstrate, let’s run this query:

MySQL

mysql> explain select * from salariesG
*************************** 1. row ***************************
[…]

Read more

EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications

Ready for another post in the EXPLAIN FORMAT=JSON is Cool series! Great! This post will discuss how to see all the information that is contained in optimized queries with
UNION using the
union_result and
query_specifications commands.
 
When optimizing complicated queries with
UNION, it is easy to get lost in the regular
EXPLAIN  output trying to identify which part of […]

Read more

EXPLAIN FORMAT=JSON has details for subqueries in HAVING, nested selects and subqueries that update values

Over several previous blog posts, we’ve already discussed what information the 
EXPLAIN FORMAT=JSON output provides for some subqueries. You can review those discussions here, here and here. EXPLAIN FORMAT=JSON shows many details that you can’t get with other commands. Let’s now finish this topic and discuss the output for the rest of the subquery types.
First, let’s look at the […]

Read more

ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing

EXPLAIN FORMAT=JSON

We’ve already discussed using the ORDER BY clause with subqueries. You can also, however, use the 
ORDER BY clause with sorting results of one of the columns. Actually, this is most common way to use this clause.
Sometimes such queries require using temporary tables or filesort, and a regular
EXPLAIN  clause provides this information. But it doesn’t show if […]

Read more

grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY

In the previous EXPLAIN FORMAT=JSON is Cool! series blog post, we discussed the  
group_by_subqueries  member (which is child of
grouping_operation). Let’s now focus on the 
grouping_operation  and other details of 
GROUP BY  processing.

grouping_operation simply shows the details of what happens when the 
GROUP BY clause is run:

MySQL

mysql> explain format=json select dept_no from dept_emp group by dept_noG
*************************** 1. row ***************************
EXPLAIN: {
[…]

Read more