EmergencyEMERGENCY? Get 24/7 Help Now!

JOIN Performance & Charsets


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

We have written before about the importance of using numeric types as keys, but maybe you’ve inherited a schema that you can’t change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries.

Take the following example, using the InnoDB storage engine:

t1 has been populated with 100,000 records, while t2 has 400,000. The data set fit easily in memory.

Notice the differences in key_len and the explicit call to CONVERT() in the WHERE clause. This is a result of the joined columns being of different character sets.

The above query took an average of 4.33 seconds to execute with t1 as utf8 and t2 as latin1. Converting both tables to utf8 resulted in an average execution time of 3.12 seconds and had the following EXPLAIN:

Notice here how there is no CONVERT() required and the key_len on both tables match. Just this simple change resulted in more than a 25% improvement in average execution time, from 4.33 to 3.12 seconds.

This test was performed with MySQL 5.0.67, FreeBSD 7, on a box with 2GB RAM.

Share Button
PREVIOUS POST
NEXT POST


Ryan Lowe

Ryan was a principal consultant and team manager at Percona until July 2014. He has experience with many database technologies in industries such as health care, telecommunications, and social networking.



Tags:

, , ,

Categories:


Comments
  • I have one problem with join.

    I need to reduce query time to it optimized level. Details are as under

    QUERY:
    SELECT SQL_CALC_FOUND_ROWS p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, 0 AS prodgroupdiscount, pi.* , (IF(p.prodname=’gold’, 10000, 0) + IF(p.prodcode=’gold’, 10000, 0) + ((MATCH (ps.prodname) AGAINST (‘gold’)) * 10) + MATCH (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST (‘gold’)) AS score FROM products p LEFT JOIN product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) INNER JOIN product_search ps ON p.productid = ps.productid WHERE p.prodvisible = 1 AND (ps.prodcode = ‘gold’ OR TRUE) AND (MATCH (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST (‘gold’)) ORDER BY score DESC LIMIT 20
    EXECUTION TIME: 2.5000+ seconds
    TABLES DATA:
    products: 31,000 records
    product_images: 92,000 records
    product_search: 57,000 records

    EXPLAIN COMMAND WITH ABOVE QUERY:
    1 SIMPLE ps fulltext prodname prodname 0 1 Using where; Using temporary; Using filesort
    1 SIMPLE p eq_ref PRIMARY,i_products_rating_vis,i_products_added_vis,i_products_sortorder_vis PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where
    1 SIMPLE pi ref i_product_images_imageprodid i_product_images_imageprodid 5 shoppingcart_5521.p.productid,const 1

    Reply

  • Any insight into this CHARSET perfomance issue related to ENUMs?

    http://bugs.mysql.com/bug.php?id=55606

    Reply

Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.