Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 35 Next 5 Older Entries

Displaying posts with tag: Query Optimization (reset)

MySQL 5.7 & Workbench 6.1 Query Plans
Employee +3 Vote Up -0Vote Down

MySQL 5.7 and Workbench 6.1 work together to provide an even prettier version of a query plan than the impressive stuff from the 5.6/6.0 combo

Here is a sneak peek at MySQL Workbench 6.1′s VISUAL EXPLAIN.

Recently I was demonstrating the difference between using EXPLAIN and VISUAL EXPLAIN to a full room at the fantastic SkiPHP Conference in Salt Lake City. MySQL 5.6 and Workbench 6.0 combine to make an easy to read graphic that aids in understanding the Query Plan Generated by the Optimizer. All in the audience agrees that the ASCII-ish output of EXPLAIN paled in comparison to VISUAL EXPLAIN. Now MySQL 5.7 and Workbench 6.1 work together to provide an even better VISUAL EXPLAIN.

  [Read more...]
Optimizing MySQL: Batching your write queries
+0 Vote Up -0Vote Down
One optimization that I’ve employed several times involves batching write queries. MySQL has some very efficient ways to load multiple rows of data in a single query. Multi-row inserts are one common way to do it, but if you’re adventurous you can also try using LOAD DATA INFILE. Multi-row inserts are just what you would [...]
OurSQL Episode 126: Subqueries and Tracking
+3 Vote Up -0Vote Down

This week we present part 2 of optimizer enhancements in MySQL 5.6, including subquery optimizations and the new optimizer trace feature. Ear Candy talks about what "hole punching" is with regards to file systems. In At the Movies we present "How to Deal with Difficult People".

Events
Galera, SkySQL and MariaDB are doing a Road Show in Stockholm on February 7th.

Oracle's doing more MySQL tech tours. These seminars will be in the mornings, and are free. They will be on:
Friday, February 15th near Milan

read more

OurSQL Episode 113: A JS API with JDD
+2 Vote Up -0Vote Down

While we were at MySQL Connect last month, we interviewed long-time MySQL developer JD Duncan about integrating NoSQL and JavaScript with MySQL. His team developed the Node.js API for MySQL. This week, we present the interview we did. In Ear Candy, we discuss what makes a senior DBA, and in At the Movies we present a ScaleBase webinar.

Events
Oracle's "Scale with MySQL" seminars:
Bucharest Romania, Tuesday 13 November
Madrid, Tuesday 27 November

read more

OurSQL Episode 108: Legendary Searches
+2 Vote Up -0Vote Down

This week we present the Sphinx Storage Engine. Ear Candy is a gotcha about setting variables in the configuration file, and At the Movies is a video about MariaDB.

Events
MySQL Connect will be held in San Francisco on Saturday September 29th and Sunday September 30th. The schedule is now online.

read more

OurSQL Episode 96: Talking about Nothing
+1 Vote Up -0Vote Down

This week we talk about NULLs and how to find the right data type.

Events
OSCon will be held Monday Jul 16th through Friday Jul 20th and has a data track. We did a podcast about OSCon, including a discount code!

MySQL Connect will be held in San Francisco on Saturday September 29th and Sunday September 30th. This is a technical conference about MySQL, bringing together MySQL engineers at Oracle and the MySQL Community, and will include sessions about the latest MySQL features and roadmaps. The


  [Read more...]
OurSQL Episode 83: The NewSQL World
+1 Vote Up -0Vote Down

MariaDB announced their 5.3 GA and 5.5 alpha releases.

Oracle will be at DrupalCon in Denver, CO from Monday, March 19th through Friday, March 23rd.

Percona's XtraDB cluster announcement
XtraDB Cluster at the San Francisco MySQL Meetup on Wednesday, March 21st

read more

OurSQL Episode 79: Removing Evil Queries, part 3
+3 Vote Up -0Vote Down

There are a lot of events scheduled for the next few months, we are excited about the international scope and the mix of big and small, paid and free events. This week we finish explaining the output of EXPLAIN. Ear candy is sshfs, at the movies is a presentation on joins and subqueries and how to optimize them.

Free OTN MySQL Developer Day, Singapore, Tuesday Feb 21st

Free OTN MySQL Developer Day, Paris, France, Tuesday Feb 21st

read more

OurSQL Episode 78: Removing Evil Queries, part 2
+3 Vote Up -0Vote Down

Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th at the Santa Clara, CA convention center. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!
. Check out the tutorial and session schedule!

SkySQL and MariaDB Solutions Day on Friday, April 13th, 2012 in Santa Clara, CA.

read more

OurSQL Episode 77: Removing Evil Queries, part 1
+2 Vote Up -0Vote Down

Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!.

The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies will be held in Buenos Aires in June. It is called the MariaDB NoSQL & Cloud Latin American Conference and we'll bring you more information as it becomes available.

read more

mk-visual-explain … visual
+4 Vote Up -0Vote Down

I love cool toys, don’t you? Especially when they help me optimize ugly, nasty, evil looking queries :)
I use mk-visual explain to help me understand better what the h*ll is happening on my MySQL servers and how they are being butchered, but today I discovered a small yet neat tool, which I think might have been overlooked. It uses mk-visual-explain, but does it visually, enabling you to open and close sections of the explain tree structure. I’m talking about http://explain.plosquare.com/.

Thank you Jan Ploski

Because Sharing is Caring

Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL
+16 Vote Up -0Vote Down

When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.

The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.

For instance, here is a common query which could be inefficient:

SELECT id FROM t WHERE id='1';

And here is the CREATE TABLE output:

mysql> show create table tG
  [Read more...]
Videos of Pythian Sessions from the 2010 O’Reilly MySQL Conference and Expo
+2 Vote Up -0Vote Down

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

TitlePresenterSlidesVideo link
(hr:min:sec)Details (Conf. site link)

Main Stage
Keynote: Under New Management: Next Steps for the CommunitySheeri K. Cabral (Pythian)N/A18:16
session 14808Ignite talk: MySQLtuner 2.0Sheeri K. Cabral (Pythian)PDF5:31N/A
Interview
Thoughts on Drizzle and

  [Read more...]
Video: Chasing Bottlenecks
+0 Vote Up -0Vote Down

Video for the presentation at the 2009 MySQL Camp:
Chasing Bottlenecks
by Morgan Tocker

Description:
The best way to performance tune a system is to find out what your bottlenecks are, and attacking those first. In the first part of this session, I'll be looking at some of the issues faced with common database workloads. From there, I'll then be showing how you can get more information out of MySQL and your Operating System to find out about your workload. This session is designed for beginner to intermediate MySQL users.

read more

Video: Top 10 MySQL Pet Peeves and How to Workaround Them
+0 Vote Up -0Vote Down

Video for the presentation at the 2009 MySQL Camp:
Top 10 MySQL Pet Peeves and How to Workaround Them
Jeremy Zawodny

Watch it online here:

Download the 106 Mb .mov file at

read more

Video: Advanced Query Manipulation with MySQL Proxy
+0 Vote Up -0Vote Down

Video for the presentation at the 2009 MySQL Conference

Advanced Query Manipulation with MySQL Proxy
Kay Roepke (Sun Microsystems)

The official conference page is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7040

Play the video directly in your browser
Download the video

Video: Testing with the MySQL Random Query Generator
+0 Vote Up -0Vote Down

Video from the 2009 MySQL Conference presentation:

If You Love It, Break It: Testing MySQL with the Random Query Generator Philip Stoev (Sun Microsystems)

The description is at:
http://www.mysqlconf.com/mysql2009/public/schedule/detail/6363

Download the presentation slides (ppt).

Real Time Data Warehousing Presentation
+0 Vote Up -0Vote Down

At the March Boston MySQL User Group meeting, Jacob Nikom of MIT's Lincoln Laboratory presented "Optimizing Concurrent Storage and Retrieval Operations for Real-Time Surveillance Applications." In the middle of the talk, Jacob said he sometimes calls what he did in this application as "real-time data warehousing", which was so accurate I decided to give that title to this blog post.

The slides can be downloaded in PDF format (1.3 Mb) at http://www.technocation.org/files/doc/Concurrent_database_performance_02.pdf. The 54 minute video can be downloaded (644Mb) at http://technocation.org/node/693/download or streamed directly in your browser at

  [Read more...]
Video: Partitioning in MySQL 5.1
+0 Vote Up -0Vote Down

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The sildes are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The 380.6 Mb .mov movie (1 hr 16 min) can be played directly in your browser at http://technocation.org/node/671/play or downloaded at http://technocation.org/node/671/download.

read more

How to Stop Hating MySQL
+0 Vote Up -0Vote Down

At LISA 2008, I gave a presentation entitled "How to Stop Hating MySQL: Fixing Common Mistakes and Myths".

The presentation slides can be downloaded as a PDF at:

http://technocation.org/files/doc/stophatingmysql.pdf

View the video online at http://technocation.org/node/646/play or download the 202.5 MB Flash video file (.flv) directly at http://technocation.org/node/646/download.

Here are some notes and links I referred to:

read more

Best way to visualize EXPLAIN?
+0 Vote Up -0Vote Down
Interpreting the output of the MySQL EXPLAIN command can be tricky. From all the information you get, some of the most important information is:

  • Full table scans
  • High join size product
  • Using filesorts
  • Using temporary tables

This can be hard to see in the raw output. Example query:

EXPLAIN SELECT b.item_id, b.bidder_id, u.username, MAX(price) AS price FROM bid b JOIN user u ON (b.bidder_id = u.user_id) WHERE b.item_id = '1' GROUP BY b.bidder_id ORDER BY price DESC

The explain outputs:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, b, ALL, null, null, 0, null, 22660, Using where; Using temporary; Using filesort
1, SIMPLE, u, eq_ref, PRIMARY, PRIMARY, 4, heavybidder.b.bidder_id, 1,

We've been experimenting












  [Read more...]
The Top 20 Design Tips for Enterprise Data Architects
+0 Vote Up -0Vote Down

At the 2008 MySQL Conference and Expo, Ronald Bradford delivered "The Top 20 Design Tips for Enterprise Data Architects". See the slides on the Forge at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Top_20_DB_Design_Tips_Every_Architect_Needs_to_Know

Video: Addressing Challenges of Data Warehousing - a Panel Discussion
+0 Vote Up -0Vote Down

At the 2008 MySQL Conference and Expo, there was a panel discussion on "Addressing Challenges of Data Warehousing - a Panel Discussion" including:

Robin Schumacher (Sun/MySQL) (moderator)

Brian Miezejewski (MySQL), Charles Hooper (Pro Relational Systems), Paul Whittington (NitroSecurity, Inc.), Raj Cherabuddi (Kickfire), Victoria Eastwood (InfoBright Inc.)

Video: Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
+0 Vote Up -0Vote Down

At the 2008 MySQL User Conference and Expo, Farhan Mashraqi spoke about "Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Optimizing_MySQL_and_InnoDB_on_Solaris_10_for_World.27s_Largest_Photo_Blogging_Community

Video: The MySQL Query Cache
+0 Vote Up -0Vote Down

At the 2008 MySQL User Conference and Expo, Baron Schwartz spoke on "The MySQL Query Cache". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#The_MySQL_Query_Cache

MySQL Proxy Presentation at the September 2007 Boston MySQL User Group
+0 Vote Up -0Vote Down

I have finally managed to watch and slightly edit the September 2007 Boston MySQL User Group presentation I did on the MySQL Proxy.

It's geared towards beginners, and has lots of examples, including explaining some of the examples that come bundled with the MySQL Proxy.

Direct Play

Download video (.wmv file, 612 Mb)
Download video (.wmv file, 76.10 Mb)

Enjoy!

Some resources:
Presentation Slides PowerPoint (ppt) or PDF or


  [Read more...]
Picking Up Where You Left Off??
+0 Vote Up -0Vote Down

I started this as a response to Keith Murphy’s post at http://www.paragon-cs.com/wordpress/?p=54, but it got long, so it deserves its own post. The basic context is figuring out how not to cause duplicate information if a large INSERT statement fails before finishing.
Firstly, the surefire way to make sure there are no duplicates if you have a unique (or primary) key is to use INSERT IGNORE INTO.
Secondly, I just experimented with adding an index to an InnoDB table that had 1 million rows, and here’s what I got (please note, this is one experience only, the plural of “anecdote” is *not* “data”; also I did this in this particular order, so there may have been caching taking place):  More »

Top 10 MySQL Best Practices
+0 Vote Up -0Vote Down

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.
For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.  More »

Progress report on High Performance MySQL, Second Edition
+0 Vote Up -0Vote Down

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

Making Queries 45-90 Times Faster!!
+0 Vote Up -0Vote Down

aka…..”when good queries go bad!”
So, today the developers were debugging why a script was running much longer than expected. They were doing text database inserts, and got to the point where they realized that double the amount of text meant the queries took double the amount of time.
You see, they were doing similar text inserts over and over, instead of using connection pooling and/or batching them. Apparently the other DBA explained that it was a limitation of MySQL, but either the developers didn’t convey what they were doing well, or the DBA didn’t think to mention batching.  More »

Showing entries 1 to 30 of 35 Next 5 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.