Performant Animations Using KUTE.js: Part 4, Animating Text

In the second tutorial of this series, you learned how to animate different CSS properties of the elements on a webpage using KUTE.js. You learned how to animate all the transform properties as well as properties like border-radius and border-color. You can also use the CSS plugin to animate CSS properties like font-size, line-height, letter-spacing, and word-spacing.

KUTE.js also has a Text plugin which allows you to animate the text inside different elements, either by increasing or decreasing a number like in a countdown or by writing a string character by character.

In this tutorial, you will learn how to animate the text inside different elements on a webpage using the CSS and Text plugins in KUTE.js.

Animating CSS Text Properties

As I mentioned earlier, you can use the KUTE.js CSS plugin to animate four different text-related CSS properties. These properties are font-size, line-height, letter-spacing, and word-spacing. We will also use some properties from the core engine discussed in the first tutorial to animate individual letters. Let’s see how we can use all these concepts together to create the vibrating HELLO text in the following demo.

Here is the code that was used to create the above animation:

Each letter of the word is wrapped inside a span tag and has its own unique class. The first tween animates the color of all the letters from white to red with an offset of 200ms. This is also the first animation that is played after clicking on Start Animation. The animateFontSize tween has been chained to animateColor. This way, the font-size animation begins as soon as the color animation ends. 

You might have noticed that I have used two attributes called repeat and yoyo to control the behavior of the animation. The yoyo attribute is used to reverse the animation that is currently being played repeatedly. This can avoid sudden jumps in the values of different properties during the animation and make it appear smooth.

The font-size animation has been chained with animateSkewing, which skews all the letters by -15 degrees. The skewX and skewY properties are available within the core engine itself.

All the tweens for animating the color of different letters have been chained to animateSkewing at once. This way, you can make sure that all the chained color animations start playing as soon as the skew animation ends. Finally, the lettersSqueezed tween reduces the spacing between different letters by 15 px.

You can create more interesting effects by using different combinations of properties.

Animating Numbers

You can also animate numbers in KUTE.js. However, you will have to include an additional text plugin to create the animation. 

The process of animating numbers is actually very simple. You just need to specify the selector where the animating numbers should be shown as well as the final number at which the animation should end. 

Here is a basic example that shows the total number of airports in the USA in 2016 using animation.

You can also apply the usual tween options like duration, repeat, and delay to customize the behavior of the animation. The code we just wrote will result in the following animation:

Writing Text Character by Character

This is a very popular effect that you can find on quite a few websites. The KUTE.js text plugin allows you to specify the new sentence that should replace the original sentence one character at a time. 

Before replacing the initial characters with their final value, random characters are animated like the numbers example you just saw. The embedded CodePen demo should make it clearer:

Here is the code that you need to write in order to create the above animation:

The character animation for the whole sentence is finished within 5 seconds. As you might have noticed, the initial and final sentences don’t need to have the same number of characters. This gives us a lot of liberty when setting the value of the text parameter.

You can also include HTML tags inside the value of the text parameter and then use CSS to change the appearance of the text that you just animated.

There will be a delay in the appearance of Earth after of has already appeared. This happens because the plugin also writes <span class="earth"> using the same character animation, but none of those characters are actually visible to the user. The delay may or may not be desirable based on your preferences.

The intermediate characters that are shown during the animation are lowercase alphabetical values by default. This can be an issue when the characters that you want to animate are all uppercase letters or numbers. Which intermediate characters are used for the animation is determined by the value of the textChars parameter. It accepts six different values:

  • alpha: In this case, the intermediate characters will be lowercase letters.
  • upper: In this case, the intermediate characters will be uppercase letters.
  • numeric: In this case, numerical characters are used for the animation. This is different from animating a number as the values won’t increase sequentially.
  • symbols: In this case, the plugin will use characters like #, %, and $ for the animations.
  • all: You can use this value if you want the intermediate characters to be a mix of alphabetic, numeric, and symbols.
  • If nothing else works for you, KUTE.js gives you the option of specifying your own custom list of characters that should be used during the animation.

The following example shows how you can animate text inside a heading using uppercase intermediate characters.

Final Thoughts

In this tutorial, you learned how to use the CSS and Text plugins in KUTE.js to animate the text inside an element. When you want to animate the appearance of the text, you need to use the CSS plugin. This will allow you to use properties like font-size, letter-spacing, etc. When you want to change the actual characters inside any element, you need to use the text plugin.

If you’re looking for additional JavaScript resources to study or to use in your work, check out what we have available on Envato Market.

I hope you learned something new in this tutorial. If you have any questions, please let me know in the comments.


Source: Nettuts Web Development

Case Study: Optimizing CommonMark Markdown Parser with Blackfire.io

As you may know, I am the author and maintainer of the PHP League‘s CommonMark Markdown parser. This project has three primary goals:

  1. fully support the entire CommonMark spec
  2. match the behavior of the JS reference implementation
  3. be well-written and super-extensible so that others can add their own functionality.

This last goal is perhaps the most challenging, especially from a performance perspective. Other popular Markdown parsers are built using single classes with massive regex functions. As you can see from this benchmark, it makes them lightning fast:

LibraryAvg. Parse TimeFile/Class Count
Parsedown 1.6.02 ms1
PHP Markdown 1.5.04 ms4
PHP Markdown Extra 1.5.07 ms6
CommonMark 0.12.046 ms117

Unfortunately, because of the tightly-coupled design and overall architecture, it’s difficult (if not impossible) to extend these parsers with custom logic.

For the League’s CommonMark parser, we chose to prioritize extensibility over performance. This led to a decoupled object-oriented design which users can easily customize. This has enabled others to build their own integrations, extensions, and other custom projects.

The library’s performance is still decent — the end user probably can’t differentiate between 42ms and 2ms (you should be caching your rendered Markdown anyway). Nevertheless, we still wanted to optimize our parser as much as possible without compromising our primary goals. This blog post explains how we used Blackfire to do just that.

Profiling with Blackfire

Blackfire is a fantastic tool from the folks at SensioLabs. You simply attach it to any web or CLI request and get this awesome, easy-to-digest performance trace of your application’s request. In this post, we’ll be examining how Blackfire was used to identify and optimize two performance issues found in version 0.6.1 of the league/commonmark library.

Let’s start by profiling the time it takes league/commonmark to parse the contents of the CommonMark spec document:

Initial benchmark of league/commonark 0.6.1

Later on we’ll compare this benchmark to our changes in order to measure the performance improvements.

Quick side-note: Blackfire adds overhead while profiling things, so the execution times will always be much higher than usual. Focus on the relative percentage changes instead of the absolute “wall clock” times.

Optimization 1

Looking at our initial benchmark, you can easily see that inline parsing with InlineParserEngine::parse() accounts for a whopping 43.75% of the execution time. Clicking this method reveals more information about why this happens:

Detailed view of InlineParseEngine::parse()

Here we see that InlineParserEngine::parse() is calling Cursor::getCharacter() 79,194 times — once for every single character in the Markdown text. Here’s a partial (slightly-modified) excerpt of this method from 0.6.1:

public function parse(ContextInterface $context, Cursor $cursor)
{
    // Iterate through every single character in the current line
    while (($character = $cursor->getCharacter()) !== null) {
        // Check to see whether this character is a special Markdown character
        // If so, let it try to parse this part of the string
        foreach ($matchingParsers as $parser) {
            if ($res = $parser->parse($context, $inlineParserContext)) {
                continue 2;
            }
        }

        // If no parser could handle this character, then it must be a plain text character
        // Add this character to the current line of text
        $lastInline->append($character);
    }
}

Blackfire tells us that parse() is spending over 17% of its time checking every. single. character. one. at. a. time. But most of these 79,194 characters are plain text which don’t need special handling! Let’s optimize this.

Instead of adding a single character at the end of our loop, let’s use a regex to capture as many non-special characters as we can:

public function parse(ContextInterface $context, Cursor $cursor)
{
    // Iterate through every single character in the current line
    while (($character = $cursor->getCharacter()) !== null) {
        // Check to see whether this character is a special Markdown character
        // If so, let it try to parse this part of the string
        foreach ($matchingParsers as $parser) {
            if ($res = $parser->parse($context, $inlineParserContext)) {
                continue 2;
            }
        }

        // If no parser could handle this character, then it must be a plain text character
        // NEW: Attempt to match multiple non-special characters at once.
        //      We use a dynamically-created regex which matches text from
        //      the current position until it hits a special character.
        $text = $cursor->match($this->environment->getInlineParserCharacterRegex());

        // Add the matching text to the current line of text
        $lastInline->append($character);
    }
}

Once this change was made, I re-profiled the library using Blackfire:

Post-optimization profile

Okay, things are looking a little better. But let’s actually compare the two benchmarks using Blackfire’s comparison tool to get a clearer picture of what changed:

Before-and-after comparison

This single change resulted in 48,118 fewer calls to that Cursor::getCharacter() method and an 11% overall performance boost! This is certainly helpful, but we can optimize inline parsing even further.

Continue reading %Case Study: Optimizing CommonMark Markdown Parser with Blackfire.io%


Source: Sitepoint

Performant Animations Using KUTE.js: Part 3, Animating SVG

The previous tutorial of the series showed you how to animate different CSS properties of any element using KUTE.js. However, the core engine does not allow you to animate properties that are specific to SVG elements. Similarly, you can’t animate the SVG morphing of different path shapes or the drawing of different SVG elements using strokes. You will have to use the KUTE.js SVG plugin to achieve any of these tasks.

Before we begin, keep in mind that you will have to include both the KUTE.js core engine and the SVG plugin for the examples in this tutorial to work.

Morphing SVG Shapes

Morphing one SVG shape into another is a very common feature that you will come across. The KUTE.js SVG plugin gives us everything that we need to create our own morphing animations with ease. 

There are three ways to morph SVG shapes using this library:

  1. You can use the fromTo() method to specify both the initial and the final SVG path for your element. 
  2. You can also use the to() method and avoid specifying the initial path. In this case, the start value for the morphing will be determined based on the value of the d attribute of the selected element that you want to morph. 
  3. One more option that you have is to pass the final path as a string directly to the tween. This way, you can avoid having two different paths in your SVG.

During initialization, the library samples some points based on the paths that we provided. These points are then stored in two different arrays. Finally, these arrays are used for the interpolation. There are a number of options that you can configure to control the morphing behavior for different paths.

  • morphPrecision: As you might have guessed, this option allows you to specify the precision or accuracy of the morphing. It is specified as a number, and a lower value means higher precision. Keep in mind that higher precision will result in more accuracy, but it will also be detrimental to the performance. This option does not apply when you are dealing with polygonal shapes or paths where the d attribute consists only of hl, and v. In such cases, the original polygon paths are used instead of sampling new ones.
  • reverseFirstPath: You can set the value of this option to true in order to reverse the drawing path for your first shape. Its default value is false.
  • reverseSecondPath: You can set the value of this option to true in order to reverse the drawing path for your second shape. Its default value is also false.
  • morphIndex: Sometimes, the points on a path might have to cover a lot of distance during morphing. You can control this behavior using the morphIndex parameter. When specified, this parameter allows you to rotate the final path in such a way that all the points travel the least distance possible.

Let’s use what we have learned so far to morph a battery icon into a bookmark icon. You should note that I have used lowercase l in order to specify the path in relative terms. This is the required markup:

The following JavaScript creates the tween object and starts the animation on button click:

Here is a demo that shows the above code in action. I have also added an extra element where the morph animation sets reverseFirstPath to true. This will help you understand the overall impact of different configuration options on the morphing. The animation duration has been set to 5 seconds so that you can closely observe both the animations and spot the differences.

In the previous example, the main path did not have any subpaths. This made the morphing very straightforward. However, this might not always be the case. 

Let’s add an extra subpath to our bookmark as well as the battery icon. If you morph the icons now, you will see that only the first subpath animates. The second subpath just disappears at the beginning of the animation and reappears at the end. The only way to animate all the subpaths in such cases is by changing the subpaths into individual paths. Here is an example:

Animating SVG Strokes

Another popular SVG-related animation effect includes starting from nothing and then drawing a predefined shape using SVG strokes. This can be used to animate the drawing of logos or other objects. In this section, you will learn how to use KUTE.js to create a stroking animation for the Font Awesome bicycle icon

There are three ways to animate SVG strokes in KUTE.js. You can animate from 0% to 100% by setting the fromTo values as 0% 0% and 0% 100%. You can also draw a part of the SVG shape by setting the values to something like 0% 5% and 95% 100%. Finally, you can set the ending value to 0% 0% in order to create an erasing effect instead of a drawing effect.

Here is the JavaScript code that I have used to animate our bicycle:

As you can see in the example below, you don’t need to worry about multiple subpaths inside a path. KUTE.js animates all of these subpaths individually without any issues. The animation duration is used to determine the time for the animation of the longest path. The stroke duration for the rest of the subpaths is then determined based on their length.

Animating SVG Transforms

We have already learned how to animate CSS transform values in the second tutorial of the series. The KUTE.js SVG plugin also allows you to use the svgTransform attribute in order to rotate, translate, scale, or skew different SVG elements on a webpage.

The rotate attribute accepts a single value that determines the angle of rotation. By default, the rotation happens around the center point of the element, but you can specify a new center of rotation using the transformOrigin attribute.

The translate attribute accepts the values in the format translate: [x, y] or translate: x. When provided with a single value, the value of y is assumed to be zero.

When skewing elements, you will have to use skewX and skewY. There is no support for skew[x, y] in SVG. Similarly, the scale attribute also accepts only one value. The same value is used to scale the elements in both x and y directions.

Here is a code snippet that applies all these transformations on a rectangle and a circle.

I have set the yoyo parameter to true so that after playing the animation in reverse, the transform properties are set to their initial value. This way, we can replay the animations again and again by clicking on the buttons.

If you press the Rotate button in the demo, you will notice that it does not seem to have any effect on the circle. To observe the rotation of circle, you will have to apply a skew transform on it in order to change its shape and then click on rotate immediately.

Final Thoughts

We began this tutorial by covering the basics of SVG morphing and stroke animations. You learned how to properly morph complex paths that have subpaths and how we can create an erasing stroke effect instead of a drawing one by choosing the right values for the draw attribute. After that, we discussed how we can use the svgTransform attribute in order to animate different transforms.

In various tutorials, we’ve seen just how powerful JavaScript has become. It’s not without its learning curves, and there are plenty of frameworks and libraries to keep you busy, as well. If you’re looking for additional resources to study or to use in your work, check out what we have available on Envato Market.

The tutorial was meant to introduce you to all the features of the KUTE.js SVG plugin and help you get started quickly. You can learn more about the SVG plugin by reading the documentation.


Source: Nettuts Web Development

How to Optimize Docker-based CI Runners with Shared Package Caches

At Unleashed Technologies we use Gitlab CI with Docker runners for our continuous integration testing. We’ve put significant effort into speeding up the build execution speeds. One of the optimizations we made was to share a cache volume across all the CI jobs, allowing them to share files like package download caches.

Continue reading %How to Optimize Docker-based CI Runners with Shared Package Caches%


Source: Sitepoint

New Course: Code a Quiz App With Vue.js

Single-page web apps are more and more in demand, but they can be hard to build with vanilla JavaScript. Thankfully, there are some powerful frameworks that can make the task easier, such as Vue.js. Learn how to use it in our new course, Code a Quiz App With Vue.js.

What You’ll Learn

In this course, Derek Jensen will show you how easy it is to code an app with the Vue.js framework. 

Making a quiz app in Vue

You’ll learn how to create a simple front-end web app using the Vue command-line interface (CLI), create self-contained components, dynamically update the user interface, and even create a simple yet elegant user experience with Semantic UI. 

Follow along and you’ll see how, with just a few simple skills, you too can begin building web apps with Vue.

Watch the Introduction

 

Take the Course

You can take our new course straight away with a subscription to Envato Elements. For a single low monthly fee, you get access not only to this course, but also to our growing library of over 1,000 video courses and industry-leading eBooks on Envato Tuts+. 

Plus you now get unlimited downloads from the huge Envato Elements library of 400,000+ creative assets. Create with unique fonts, photos, graphics and templates, and deliver better projects faster.


Source: Nettuts Web Development

Performant Animations Using KUTE.js: Part 2, Animating CSS Properties

The first tutorial of the series focused on providing a beginner-friendly introduction to the KUTE.js library. In that tutorial, we only animated the opacity and rotateZ property for our elements. In this tutorial, you will learn how to animate the rest of the CSS properties using KUTE.js. 

Some of these properties will require you to load the CSS plugin, while others can be animated using the core engine itself. Both these scenarios will be discussed separately in the tutorial.

Box Model Properties

The core KUTE.js engine can animate only the most common box model properties: width, height, top, and left. You will have to use the CSS plugin to animate almost all other box model properties. Here is an example that animates the top position, width and height of our boxes from the previous tutorial:

You might have noticed that I used allFromTo() to animate the top property of all the boxes. However, I used fromTo() to animate individual boxes. You should remember that the boxes stay in their final state once the animation completes.

With the help of the CSS plugin, you will be able to animate margin, padding, and borderWidth as well. Once you have included the plugin in your project, the rest of the process is exactly the same.

Animating Transform Properties

You can animate almost all the transform properties mentioned in the spec with the help of the core engine itself. There is no need to load the CSS plugin in this case.

You can animate the element translation in 2D space using translate. Similarly, you can use translateX, translateY, and translateZ in order to animate the elements along the respective axes. For the translateZ property to have any effect, you will also have to set a value for the parentPerspective property. Here is an example:

If you click the Start Animation button and observe the animation closely, you will see that the red box translates -200 in the X direction first. After that, it moves to its original position and starts translating in the Y direction. The reason for the box to animate translateX first is that we have added a delay for the translateY animation using the offset property.

Just like translation, you can also perform rotations along a specific axis using the rotate, rotateX, rotateY, and rotateZ properties. Since rotateX and rotateY are 3D rotations, you will have to use the perspective property for the rotation animation to work as expected. The following example shows how using the perspective property affects the overall animation for these two properties.

In the above example, box A and box B start their rotation along the Y axis at the same time, but the resulting animation is very different because of the perspective property. You might have noticed that the orange box is not performing the rotation around its center that was applied to it using animateAll. This is because all animations have a duration of 500 milliseconds by default, and we are applying both animateAll and animateA on the orange box at the same time. 

Since animateA is applied after animateAll, its animation takes precedence over animateAll. You will see that the common rotation using animateAll is still being applied on the orange box once you increase the animation duration. In other words, you cannot animate different transform properties using multiple tween objects at the same time. All the transform properties that you want to animate should be specified inside a single tween object. The following example should make it clear:

Using the CSS Plugin

As I mentioned earlier, not all CSS properties can be animated using only the core KUTE.js engine. You need to use an extra CSS plugin to animate properties like padding, margin, background position of images, and other border-related properties. So, before you try any of the examples in this section, you should include the plugin in your project.

Once you have included the plugin, you will be able to animate the border-radius property using borderRadius. You can also animate all the corner border-radius values individually using borderTopLeftRadius, borderTopRightRadius, borderBottomLeftRadius, and borderBottomRightRadius. Here is an example that shows the animation in action.

If you click the Start Animation button, you will notice that the top left border-radius for the red and yellow box is animated after a delay. This is because of the offset property. The rest of the radii are animated as soon as we click on the button. The above example was created using the following code:

We have not chained the tween objects together, so all the animations start at once this time. You can also animate the color of different borders in a similar manner using borderColor, borderTopColor, borderLeftColor, borderBottomColor, and borderRightColor

Final Thoughts

In this tutorial, we learned about different CSS properties that can be animated with and without the use of the KUTE.js CSS plugin. If you have any questions, please let me know in the comments.

The next tutorial will cover different animations that can be created using the KUTE.js SVG plugin.


Source: Nettuts Web Development

A Comprehensive Guide To Web Design




 


 

(This is a sponsored post). Web design is tricky. Designers and developers have to take a lot of things into account when designing a website, from visual appearance (how the website looks) to functional design (how the website works). To simplify the task, we’ve prepared this guide.

A Comprehensive Guide To Web Design

In this article, I’ll focus on the main principles, heuristics and approaches that will help you to create a great user experience for your website. I’ll start with global things like the user journey (how to define the “skeleton” of the website) and work down to the individual page (what should be considered during web page design). We’ll also cover other essential aspects of design, such as mobile considerations and testing.

The post A Comprehensive Guide To Web Design appeared first on Smashing Magazine.


Source: Smashing Magazine

Upgrade Your Project with CSS Selector and Custom Attributes

Element selectors for Selenium WebDriver are one of the core components of an automation framework and are the key to interaction with any web application. In this review of automation element selectors, we will discuss the various strategies, explore their capabilities, weigh their pros and cons, and eventually recommend the best selector strategy – custom attributes with CSS selector.

Continue reading %Upgrade Your Project with CSS Selector and Custom Attributes%


Source: Sitepoint

ProtoPie, the Hi-Fi Prototyping Tool That Will Improve Your Workflow

This article was originally published on ProtoPie. Thank you for supporting the partners who make SitePoint possible.

As a designer, bridging the gap with stakeholders is utterly important. Properly conveying design and interaction ideas quickly and rapidly with solely static UI designs, mockups, wireframes and even simple click-through prototypes simply doesn’t work.

This is what Tony Kim thought. During his time at Google as an Interaction Designer, he wanted to build highly interactive prototypes easily and quickly in order for him to be able to share his ideas clearly and ultimately bridge the gap between him and his stakeholders.

The tools at his disposal didn’t allow him to do this easily and quickly. Easy tools didn’t provide the high-fidelity Kim was looking for while other tools for more advanced prototyping usually had a steep learning curve and/or required coding, leading to a lengthy prototyping process.

This is how brainchild ProtoPie was born.

This article will give you a brief overview on what ProtoPie is, its philosophy, and why you should adopt ProtoPie as your primary prototyping tool to improve your workflow.

What is ProtoPie?

ProtoPie is a powerful hi-fi prototyping tool on Mac and Windows for mobile apps that empowers designers to build the most advanced, highly interactive prototypes easily and quickly deployable and shareable on any device while utilizing smart device sensors.

ProtoPie Interface

The philosophy behind ProtoPie is that high-fidelity prototyping should be done easily and quickly.

Tony Kim, founder of ProtoPie, explains: “I believe in hi-fi prototyping. The prototypes that any designer should make are the ones that resemble the real deal, in regards to the way the user interacts.”

As hi-fi interactions are key in the design process, the golden formula of ProtoPie surrounding interactions is simple, straightforward and runs like a thread through ProtoPie:

interaction = object + trigger + response

ProtoPie is intuitive

This concept model serves as the foundation of ProtoPie’s user interface lowering the threshold to build high-fidelity prototypes while making the learning curve truly gradual. Due to ProtoPie’s ease of use, gradual learning curve and intuitive user interface, ProtoPie won the Red Dot Award 2017 for Interface Design.

ProtoPie is Versatile

ProtoPie code free

What Can ProtoPie Do?

As you might know already by now, the core purpose of ProtoPie is to empower designers to build hi-fi prototypes easily and quickly.

Many designers out there still believe that advanced prototyping without coding is not possible. This is simply not true. By piecing some hi-fi interactions together, you may already have a working interactive prototype within minutes.

See here how ProtoPie shows how easy it is to create interactions according to its golden formula that you can test right away.

ProtoPie has easy to use features

ProtoPie distinguishes itself from other tools out there by supporting sensors in smart devices. To make prototypes feel as if they are the real deal when deploying on any smart device, built-in sensors simply need to be taken into consideration. The sensors that are supported by ProtoPie are Tilt, Sound, Compass, 3D Touch and Proximity.

ProtoPie feature sensor

But it doesn’t stop there. You can create interactions across multiple devices using ProtoPie. This provides designers with more freedom on creating high-fidelity prototypes. Using the Send response and Receive trigger in ProtoPie, it’s possible to send and receive present messages upon establishing a link among devices.

ProtoPie feature bridge

Besides being able to create hi-fi prototypes easily and quickly, designers are given various options on how they can deploy and share their creations with stakeholders.

  • You can use both ProtoPie Player (available on iOS and Android) and ProtoPie Cloud to share prototypes easily with stakeholders.
  • You can run and test prototypes using ProtoPie Player, desktop browser or mobile browser.
  • You can save your prototypes locally on your device.

Thus, there are plenty of ways to deploy and share prototypes. It’s up to you how you would like to do this.

Continue reading %ProtoPie, the Hi-Fi Prototyping Tool That Will Improve Your Workflow%


Source: Sitepoint

How to Optimize SQL Queries for Faster Sites

This article was originally published on the Delicious Brains blog, and is republished here with permission.

You know that a fast site == happier users, improved ranking from Google, and increased conversions. Maybe you even think your WordPress site is as fast as it can be: you’ve looked at site performance, from the best practices of setting up a server, to troubleshooting slow code, and offloading your images to a CDN, but is that everything?

With dynamic, database-driven websites like WordPress, you might still have one problem on your hands: database queries slowing down your site.

In this post, I’ll take you through how to identify the queries causing bottlenecks, how to understand the problems with them, along with quick fixes and other approaches to speed things up. I’ll be using an actual query we recently tackled that was slowing things down on the customer portal of deliciousbrains.com.

Identification

The first step in fixing slow SQL queries is to find them. Ashley has sung the praises of the debugging plugin Query Monitor on the blog before, and it’s the database queries feature of the plugin that really makes it an invaluable tool for identifying slow SQL queries. The plugin reports on all the database queries executed during the page request. It allows you to filter them by the code or component (the plugin, theme or WordPress core) calling them, and highlights duplicate and slow queries:

Query Monitor results

If you don’t want to install a debugging plugin on a production site (maybe you’re worried about adding some performance overhead) you can opt to turn on the MySQL Slow Query Log, which logs all queries that take a certain amount of time to execute. This is relatively simple to configure and set up where to log the queries to. As this is a server-level tweak, the performance hit will be less that a debugging plugin on the site, but should be turned off when not using it.

Understanding

Once you have found an expensive query that you want to improve, the next step is to try to understand what is making the query slow. Recently during development to our site, we found a query that was taking around 8 seconds to execute!

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

We use WooCommerce and a customized version of the WooCommerce Software Subscriptions plugin to run our plugins store. The purpose of this query is to get all subscriptions for a customer where we know their customer number. WooCommerce has a somewhat complex data model, in that even though an order is stored as a custom post type, the id of the customer (for stores where each customer gets a WordPress user created for them) is not stored as the post_author, but instead as a piece of post meta data. There are also a couple of joins to custom tables created by the software subscriptions plugin. Let’s dive in to understand the query more.

MySQL is your Friend

MySQL has a handy statement DESCRIBE which can be used to output information about a table’s structure such as its columns, data types, defaults. So if you execute DESCRIBE wp_postmeta; you will see the following results:

FieldTypeNullKeyDefaultExtra
meta_idbigint(20) unsignedNOPRINULLauto_increment
post_idbigint(20) unsignedNOMUL0
meta_keyvarchar(255)YESMULNULL
meta_valuelongtextYESNULL

That’s cool, but you may already know about it. But did you know that the DESCRIBE statement prefix can actually be used on SELECT, INSERT, UPDATE, REPLACE and DELETE statements? This is more commonly known by its synonym EXPLAIN and will give us detailed information about how the statement will be executed.

Here are the results for our slow query:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEpm2refmeta_keymeta_key576const28Using where; Using temporary; Using filesort
1SIMPLEpmrefpost_id,meta_keymeta_key576const37456Using where
1SIMPLEpeq_refPRIMARY,type_status_datePRIMARY8deliciousbrainsdev.pm.post_id1Using where
1SIMPLElrefPRIMARY,order_idorder_id8deliciousbrainsdev.pm.post_id1Using index condition; Using where
1SIMPLEseq_refPRIMARYPRIMARY8deliciousbrainsdev.l.key_id1NULL

At first glance, this isn’t very easy to interpret. Luckily the folks over at SitePoint have put together a comprehensive guide to understanding the statement.

The most important column is type, which describes how the tables are joined. If you see ALL then that means MySQL is reading the whole table from disk, increasing I/O rates and putting load on the CPU. This is know as a “full table scan” (more on that later).

The rows column is also a good indication of what MySQL is having to do, as this shows how many rows it has looked in to find a result.

Explain also gives us more information we can use to optimize. For example, the pm2 table (wp_postmeta), it is telling us we are Using filesort, because we are asking the results to be sorted using an ORDER BY clause on the statement. If we were also grouping the query we would be adding overhead to the execution.

Visual Investigation

MySQL Workbench is another handy, free tool for this type of investigation. For databases running on MySQL 5.6 and above, the results of EXPLAIN can be outputted as JSON, and MySQL Workbench turns that JSON into a visual execution plan of the statement:

MySQl Workbench Visual Results

It automatically draws your attention to issues by coloring parts of the query by cost. We can see straight away that join to the wp_woocommerce_software_licences (alias l) table has a serious issue.

Continue reading %How to Optimize SQL Queries for Faster Sites%


Source: Sitepoint