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 中文
Previous 30 Newer Entries Showing entries 31 to 50

Displaying posts with tag: explain (reset)

MySQL University: Optimizing Queries with EXPLAIN
Employee +0 Vote Up -0Vote Down

This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University
  [Read more...]
how to prevent explain from executing subqueries
+3 Vote Up -0Vote Down
Here’s a quick tip for using explain: You may know this already, but mysql will actually execute some subqueries when you invoke explain.  Here’s an example: mysql> explain select id from projects where id = (select max(id) from projects where name like 'en%'); +----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | […] Related posts:
  • Using the ENUM data type to increase performance While going through the DATA TYPES section of the Certification...
  • Related posts brought to you by Yet Another Related Posts Plugin.
    Materialized view makes login process 25k times faster
    +0 Vote Up -1Vote Down
    It may sound like a dramatic number, and that’s because it is. One thing that is enjoyable about working on beta applications is finding new solutions and better methods to improve the user experience. The original method for displaying the recent addition of overview analytics data in the beta version of Kontrollbase was to run [...]
    How (not) to find unused indexes
    +2 Vote Up -0Vote Down

    I've seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.  This method is flawed - here's the first reason why:

  • CREATE TABLE `sales` (
  • `id` int(11) NOT NULL AUTO_INCREMENT,
  • `customer_id` int(11) DEFAULT NULL,
  • `status` enum('archived','active') DEFAULT NULL,
  • PRIMARY KEY (`id`),
  • KEY `status` (`status`)
  • mysql> SELECT count(*), STATUS FROM sales GROUP BY STATUS;
  • +----------+---------+
  • | count(*) | STATUS  |
  •   [Read more...]
    EXPLAIN – An essential tool for MySQL developers.
    +1 Vote Up -0Vote Down

    Just recently I came across the presentation “Bend SQL to your will with EXPLAIN” by Ligaya Turmelle via the Linked In – MySQL Speakers and Presenters group. Slides available at Slideshare. While I know a little about MySQL, I always enjoy spending time reviewing content when possible, perhaps as a good reference when asked, perhaps to glean some small point of knowledge I didn’t know, or didn’t understand completely, or had forgotten about. It’s also fun to randomly find worthwhile information.

    EXPLAIN is an essential tool for MySQL developers, if you don’t know what

      [Read more...]
    Teaching table design with EXPECT
    Employee +0 Vote Up -0Vote Down

    The next meeting of the North Texas MySQL Users Group is next Monday and the presentation will be on designing database tables. This is part of a series for novices with MySQL and/or databases. Too often novices commit obvious sins like BIGINTs for any numeric storage field or indexing every column. EXPLAIN is a valuable tool in optimizing SELECT statements but too many DBAs do not discover it until well after they are stuck with a small, unruly group of badly designed tables.

    Using EXPLAIN is seen as a 'dark art' by too many and hopefully we can get the novices in

      [Read more...]
    Explain this
    +1 Vote Up -0Vote Down

    The EXPLAIN command is an important tool to review how a SQL query is executed and in this example includes what indexes are used.

    By adding a covering index I ended up with the following EXPLAIN plan I was unable to explain. The end result was a boost in server performance which was the ultimate goal.

    mysql> explain select max(md)  from e_r  where email = 'xxxx@gmail.com' and id = '36981';
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
    |  1 | SIMPLE      | NULL  | NULL | NULL
      [Read more...]
    Understanding Different MySQL Index Implementations
    +3 Vote Up -0Vote Down

    It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

    There are four general index types to consider when creating an appropriate index to optimize SQL queries.

    • Column Index
    • Concatenated Index
    • Covering Index
    • Partial Index

    For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

    Example Table

    For the following examples, I will use this test table structure.

      user_name VARCHAR(20) NOT NULL,
      first_name VARCHAR(30) NOT NULL,
      last_name VARCHAR(30) NOT NULL,
      external_id INT
      [Read more...]
    MySQL Query Analyzer vs. Percona’s Patches
    Employee +0 Vote Up -0Vote Down

    So it’s been a while since I’ve hit my blog, but I feel compelled to respond to Baron’s post, and many of the other (perhaps short sighted) criticisms out there against this new functionality that we’ve been working on for so long.

    Everybody seems to be saying that this functionality should be implemented in the server, or that the better way to do this is to use these patches which add functionality to the logging that MySQL already provides. Well guess what people - what does that give you, other than some more details on you queries?

    More I/O.

    What’s bad on a database server?

    More I/O.

    Query Analyzer, whilst it does currently use a proxy to collect the statistics, doesn’t hit your disk at all. Everything is collected

      [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...]
    A 5.1 QEP nicety - Using join buffer
    +0 Vote Up -0Vote Down

    I was surprised to find yesterday when using MySQL 5.1.26-rc with a client I’m recommending 5.1 to, some information not seen in the EXPLAIN plan before while reviewing SQL Statements.

    Using join buffer

    | id | select_type | table | type   | possible_keys | key          | key_len | ref                    | rows  | Extra                                        |
    |  1 | SIMPLE      | lr    | ALL    | NULL          | NULL         | NULL    | NULL                   |  1084 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE
      [Read more...]
    EXPLAIN Cheatsheet
    +0 Vote Up -0Vote Down

    At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

    For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

    * or tea, for those of us in the civilized world.

    MySQL Conference Liveblogging: EXPLAIN Demystified (Tuesday 2:00PM)
    +0 Vote Up -0Vote Down
    • Baron Schwartz presents
    • only works for SELECTs
    • nobody dares admit if they've never seen EXPLAIN
    • MySQL actually executes the query
    • at each JOIN, instead of executing the query, it fills the EXPLAIN result set
    • everything is a JOIN (even SELECT 1)
    • Columns in EXPLAIN
      • id: which SELECT the row belongs to
      • select_type
        • simple
        • subquery
        • derived
        • union
        • union result
      • table: the table accessed or its alias
      • type:
        • join
        • range
      • possible_keys: which indexes looked useful to the optimizer
      • key: which index(es) the optimizer chose
      • key_len: the number of bytes of the index MySQL will use
      • ref: which columns/constants from
      [Read more...]
    My presentations at the 2008 MySQL Conference and Expo
    +0 Vote Up -0Vote Down

    I'll be attending the 2008 MySQL Conference and Expo again this year, and I'm looking forward to hearing some great sessions, meeting new and old friends, and giving sessions myself. As a proposal reviewer, I looked at and voted on 250+ proposals for sessions and tutorials for this conference. There are going to be some great sessions and tutorials.

    Query Profiling Tools ? part 1, mysqlsla
    +0 Vote Up -0Vote Down
    The “sla” in mysqlsla stands for “statement log analyzer”. This does a much better job than mysqldumpslow of analyzing your slow query log. In fact, you can sort by many different parameters — by sheer number of times the query shows up in the slow query log, by the total or average query [...]
    MySQL Query Profiling Tools ? part 0, Ma?atkit Query Profiler
    +0 Vote Up -0Vote Down
    Today I’ve been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they’re “good” or “bad”. In my arsenal of “tools I really want to check out” has been Ma’atkit’s Query Profiler. They’re very different tools. Ma’atkit’s query [...]
    Introducing MySQL Visual Explain
    +0 Vote Up -0Vote Down

    If you've ever wished you could see MySQL's EXPLAIN output formatted as a tree, now you can. MySQL Visual Explain transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand.

    How to avoid an extra index scan in MySQL
    +0 Vote Up -0Vote Down

    Is your MySQL server doing an extra index scan on queries that need to check a key for matches or NULL? It's easy for this to happen accidentally, but it's also easy to fix, especially in MySQL 5.0 and up. Here's how.

    EXPLAIN: "Using join cache" renamed to "Using join buffer"
    +0 Vote Up -0Vote Down

    As soon as I've finished writing this post about "Using join cache", it was apparent that "Using join cache" is poor wording. First, the corresponding server variable is called @@join_buffer_size, not join cache size, and second, there is really no cache involved.

    We've had a discussion about how this should be called. Some sources use term Block nested-loops join but we've settled on "Using join buffer". Another change is that we've decided to move the note one line down to the table that "does the buffering". As a result, what was this

    mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar';
    | id | select_type | table | type  |     | Extra                         |
      [Read more...]
    Use of join buffer is now visible in EXPLAIN
    +0 Vote Up -0Vote Down

    * s/Using join cache/Using join buffer/, changed to show the final variants of EXPLAIN output as described here
    * s/join_buff_size/join_buffer_size/

    Starting from 5.1.18, EXPLAIN output may show "Using join cache", like in this example:

    mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar';
    | id | select_type | table | type  |     | Extra                          |
    |  1 | SIMPLE      | t1    | range |     | Using where                    |
    |  1 | SIMPLE      | t2    | range |     | Using where; Using join buffer |

      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 50

    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.