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 33 Next 3 Older Entries

Displaying posts with tag: query (reset)

MySQL Query Patterns, Optimized – Webinar questions followup
+3 Vote Up -0Vote Down

On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had time for  during the session, but here are all the questions with my complete

  [Read more...]
Optimal index size for variable text in MySQL
+2 Vote Up -0Vote Down

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read the rest »

Can MySQL use primary key values from a secondary index?
+2 Vote Up -0Vote Down

In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table’s primary key are concatenated to the index. I.e. the primary key contents is part of every other index.

Assuming the following table structure:

CREATE TABLE `bets` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB

Here is the visualization:

If MySQL could use in queries these implicitly

  [Read more...]
Joins: inner, outer, left, right
+0 Vote Up -0Vote Down

In (My)SQL, join is a means for combining records from two tables into a single set which can be either returned as is or used in another join. In order to perform the operation a join has to define the relationship between records in either table, as well as the way it will evaluate the relationship. The relationship itself is created through a set of conditions that are part of the join and usually are put inside ON clause. The rest is determined through a join type, which can either be an inner join or an outer join.

The SQL clauses that set the respective join type in a query are [INNER] JOIN and {LEFT | RIGHT} [OUTER] JOIN. As you can see the actual keywords INNER and OUTER are optional and can be omitted, however outer joins require specifying the direction –

  [Read more...]
(My)SQL mistakes. Do you use GROUP BY correctly?
+2 Vote Up -0Vote Down

Often I see a SQL problem solved incorrectly and I do not mean inefficiently. Simply incorrectly. In many cases the developer remains unaware that they aren’t getting the results they were expecting or even if a result is correct, it is only by chance, for example because the database engine was smart enough to figure out some non-sense in a query. In a few posts I will try to disclose some of the more common problems.

Aggregate with GROUP BY

Unlike many other database systems, MySQL actually permits that an aggregate query returns columns not used in the aggregation (i.e. not listed in GROUP BY clause). It could be considered as flexibility, but in practice this can easily lead to mistakes if a person that designs queries does not understand how they will be executed. For example, what values an aggregate query returns for a

  [Read more...]
Why do threads sometimes stay in ‘killed’ state in MySQL?
+4 Vote Up -0Vote Down

Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.

Threads and connections

MySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a

  [Read more...]
Dedicated table for counters
+1 Vote Up -0Vote Down

There are a few ways to implement counters. Even though it’s not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.

A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?

The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:

mysql> SHOW CREATE TABLE hits\G
*************************** 1. row ***************************
Table: hits
Create Table: CREATE TABLE `hits` (
`cnt` int(11) NOT NULL
) ENGINE=MyISAM

mysql>
  [Read more...]
Why a statement can be unsafe when it uses LIMIT clause?
+0 Vote Up -0Vote Down

MySQL 5.1 or newer can sometimes start throwing a strange message into an error log. The message states that a query was unsafe for binary logging along with some additional information. What does it mean? Is it a problem?

From time to time you might spot MySQL error log filling with the following warning:

“[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM score WHERE user_id = 12345 AND created = ’2012-04-15′ LIMIT 1″

If binary logging is enabled and the log format is set to STATEMENT, MySQL generates such message when it considers that a query is ambiguous and could behave

  [Read more...]
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
+3 Vote Up -1Vote Down

This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look at what this optimization actually is and what benefits it brings.

Multi Range Read

With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for column values that do not

  [Read more...]
MySQL Cluster 7.2 GA Released, Delivers 1 BILLION Queries per Minute
Employee_Team +0 Vote Up -0Vote Down
0 0 1 14 83 Homework 1 1 96 14.0 Normal 0 false false false EN-GB JA X-NONE

70x Higher JOIN Performance, NoSQL Key-Value API & Cross Data Center Sharding with Synchronous Replication 

0 0 1 535 3052 Homework 25 7 3580 14.0 Normal 0 false  [Read more...]
Viewing RMAN jobs status and output
+0 Vote Up -1Vote Down

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.


  [Read more...]
MySQL Analytics: updated query for table engine data statistics
+1 Vote Up -0Vote Down

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001









  [Read more...]
Optimizing the MySQL IN() Comparison Operations Which Include the Indexed Field
+4 Vote Up -1Vote Down
The MySQL IN() Comparison Operator is said to be very quick if all the values are constants (the values are then evaluated and sorted first, and the search is done using a binary search). However, what if the field which the IN clause refers to, is part of the index used to execute the query? [...]
Does Size or Type Matter?
+4 Vote Up -0Vote Down

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select "1"+"1";
+---------+
| "1"+"1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression

  [Read more...]
How To Diagnose And Fix Incorrect Post Comment Counts In WordPress
+1 Vote Up -1Vote Down

Introduction

If your WordPress comment counts got messed up, whether because of a plugin (I'm talking about you, DISQUS) or you messed with your database manually and did something wrong (yup, that's what I just did), fear not – I have a solution for you.

But first, a little background.

Comment Counts In WordPress

Here's how comment counts work in WP:

  • Posts live in a table called wp_posts and each has an ID.
  • Comments reside in a table called wp_comments, each referring to an ID in wp_posts.
  • However, to make queries faster, the comment count is also cached in the wp_posts table, rather than getting calculated on every page load.
  [Read more...]
When the subselect runs faster
+1 Vote Up -1Vote Down

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT CODE:
  • SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
  • This column in the table is looks like this:

    PLAIN TEXT CODE:
  • `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL
  • The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

    PLAIN TEXT CODE:  [Read more...]
    MySQL query that get ranks today, this week and this month
    +1 Vote Up -0Vote Down

    In this article I’m showing how to retrieve result based on today, week and month using mysql query. To learn the techniques just visit http://thinkdiff.net/mysql/getting-rank-today-this-week-and-this-month/


    Getting rank today, this week and this month
    +0 Vote Up -0Vote Down

    In my previous article I’ve shown how to get rank using mysql query. Now I’m showing how to retrieve result based on today, week and month using mysql query. Actually I also implemented this in my quiz project so I’m sharing this with you.

    For the table structure please look my previous article http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

    Task 1: I’ve to retrieve those users rank who played the game today.
    Solution: Look at the query

    SELECT uid, participated, correct, wrong from quiz_user
        WHERE DAYOFMONTH(CURDATE())=extract(day from updated)
        ORDER BY correct DESC,

      [Read more...]
    Getting user’s rank using mysql query
    +1 Vote Up -0Vote Down

    In this article you’ll learn how to get user’s rank in mysql query. Check the link http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query


    [MySQL] Deleting/Updating Rows Common To 2 Tables – Speed And Slave Lag Considerations
    +1 Vote Up -0Vote Down

    Introduction

    A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

    Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

    Solutions Suggested By Others

    DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
    DELETE a FROM a INNER JOIN b on a.id=b.id;
    DELETE FROM a WHERE id IN (SELECT id FROM b)

    The Problem With  [Read more...]

    Example of a Basic MSSQL Query using PHP
    +0 Vote Up -2Vote Down

    An example of a basic MSSQL (Microsoft SQL Server/SQL Server Express) query using PHP.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    $szQry = "SELECT column1, column2 FROM foo";
    $szDBConn = mssql_connect("host","username","password");
    mssql_select_db("database_name", $szDBConn);
    $saResults = mssql_query($szQry, $szDBConn);
    while($obResults = mssql_fetch_row($saResults))
    {
       echo $obResults[0]." ".$obResults[1];
    }
    mssql_close($szDBConn);

    Comments/description of Example

    Line #1 SQL statement that will be sent to the MySQL database server. Line #2 MSSQL database login credentilas; host (127.0.0.1), username and password. The “host” is the server name or IP address of your database server. If your host has multiple  [Read more...]
    Query cache and comments
    Employee +0 Vote Up -0Vote Down

    Update

    Ok, as Morgan quickly found out: I'm incredibly stupid. Read his comment and you'll know why. Ok, you'll not know why but you'll know that I am.

    Really cool to see Chris taking up blogging as well

    He has written nice little example about inserting comments into queries to distinguish the client’s IP when they are funneled through the proxy. Reading the comments about this little trick making the query cache not work, I couldn’t help thinking that those are wrong. I vaguely remembered that in some recent version this shortcoming was fixed, so I decided to run a little test on 5.1.30 to verify:

    mysql> select
      [Read more...]
    Query cache and comments
    Employee +0 Vote Up -0Vote Down

    Update

    Ok, as Morgan quickly found out: I'm incredibly stupid. Read his comment and you'll know why. Ok, you'll not know why but you'll know that I am.

    Really cool to see Chris taking up blogging as well

    He has written nice little example about inserting comments into queries to distinguish the client’s IP when they are funneled through the proxy. Reading the comments about this little trick making the query cache not work, I couldn’t help thinking that those are wrong. I vaguely remembered that in some recent version this shortcoming was fixed, so I decided to run a little test on 5.1.30 to verify:

    mysql>
      [Read more...]
    Query cache and comments
    Employee +0 Vote Up -0Vote Down

    Update

    Ok, as Morgan quickly found out: I'm incredibly stupid. Read his comment and you'll know why. Ok, you'll not know why but you'll know that I am.

    Really cool to see Chris taking up blogging as well

    He has written nice little example about inserting comments into queries to distinguish the client’s IP when they are funneled through the proxy. Reading the comments about this little trick making the query cache not work, I couldn’t help thinking that those are wrong. I vaguely remembered that in some recent version this shortcoming was fixed, so I decided to run a little test on 5.1.30 to verify:

    mysql>
      [Read more...]
    MySQL University: Random Query Generator
    Employee +0 Vote Up -0Vote Down

    This Thursday (December 11th), Philip Stoev will talk about the Random Query Generator, a new QA tool that generates pseudo-random queries targeted to exercise a specific part of the server being tested. It then executes those queries to check for crashes or assertions or to compare the results returned from two different servers (two versions, two different storage engines, optimizer flags, etc.). It is also able to execute queries against a replication master, constantly checking that the slave is okay and has not diverged.

    Towards the end of the session, Philip will share his desktop and show some live examples. Make sure to adjust your volume in

      [Read more...]
    MySQL University for up to 100 attendees
    Employee +0 Vote Up -0Vote Down

    Yesterday (December 4th), Sergey Petrunia gave a presentation on what's new in MySQL Optimizer. Unfortunately, the slides didn't show up in the Dimdim presentation area, which also means that the recorded session only has Sergey's voice but not the slides. (I've filed a bug in Dimdim's issue tracker.) However, Sergey kept referring to slide numbers in his talk, so it should be fairly easy (just not as convenient as usual) to follow his recorded presentation.

    The slides, together with links to the recording and the chat transcript, can be found

      [Read more...]
    MySQL Enterprise Monitor documentation public now
    Employee +0 Vote Up -0Vote Down

    The MySQL Enterprise Monitor continuously monitors MySQL servers and alerts to potential problems before they impact the system. It helps eliminating security vulnerabilities, improves replication, optimizes performance, and more. Its newest feature, Quan (Query Analyzer), helps identify queries that could be tuned to improve performance. Quan enables database administrators to do the work that would otherwise require hours in just minutes, or even seconds, and it provides ongoing statistical information about the performance of your queries.

    MySQL Enterprise Monitor is a commercial offering by Sun Microsystems, and so was the documentation. To help anyone (even if they're not customers) get a better and complete understanding of what exactly MySQL Enterprise Monitor is about and what it can do, we've decided to make its full documentation publicly available.

      [Read more...]
    Random selection, with a bias ...
    +0 Vote Up -0Vote Down
    Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the
    MySQL – The GROUP_CONCAT() function
    +0 Vote Up -0Vote Down

    GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

    For example if you query:

    mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';

    It outputs:

    Language Chinese Khmer Kuy Lao

    To concatenate the values into a single string, you query:

    mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

    Then the output will be:

    Languages Chinese, Khmer, Kuy, Lao

    You can also use some format of GROUP_CONCAT(). Like

    • SELECT GROUP_CONCAT( Language SEPARATOR ‘-’ )… It will use ‘-’ instead of






      [Read more...]
    Has them all
    +0 Vote Up -0Vote Down
    A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.Say we have a table that lists all
    Showing entries 1 to 30 of 33 Next 3 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.