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 121 to 150 of 858 Next 30 Older Entries

Displaying posts with tag: Uncategorized (reset)

Query in a loop?
+0 Vote Up -0Vote Down

I ran across this gem recently on StackOverflow:

$queryxyzzy12=("SELECT * FROM visitorcookiesbrowsing ORDER by id ASC");
$resultxyzzy23=mysql_query($queryxyzzy12) or die(mysql_error());

while($ckxf = mysql_fetch_assoc($resultxyzzy23)){
$querycrtx=("SELECT * FROM cart WHERE userkey='$ckxf[usercookie]' ORDER by datebegan DESC");
$resultcrtx=mysql_query($querycrtx) or die(mysql_error());
------ snip ------
}

Besides the interesting variable names used, it’s also doing a query inside a loop, which is very inefficient. This would be better written as a single JOIN query:

SELECT 
v.*,
c.*
FROM
visitorcookiesbrowsing v
LEFT JOIN cart c ON c.userkey=v.usercookie
ORDER BY
v.id ASC,
c.datebegan DESC

The details of JOIN vs. LEFT JOIN depend on the actual
















  [Read more...]
Galera pre-deployment check
+0 Vote Up -0Vote Down

One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.

  • Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for InnoDB-internal reasons).
  • We want to know about FULLTEXT indexes. With recent InnoDB versions also supporting FULLTEXT we need to check not just whether a table has such an index, but actually which engine it is.
  • Spatial indexes. While both InnoDB and MyISAM can deal with spatial datatypes (POINT, GEOMETRY, etc), only MyISAM has the spatial indexes.

Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB

  [Read more...]
Speaking at the OpenWest Conference
+0 Vote Up -0Vote Down

I’m presenting two talks at the OpenWest Conference next month.  The first talk, on May 2nd, will be about PHP security.  The second talk, given on May 3rd, will be about database optimization, geared towards web developers.  I’ll discuss some of the same things that I have discussed on this blog.

We’re going to have some great speakers at that conference.  On the PHP front, we’ll have Rasmus Lerdorf giving a keynote as well as another presentation.  From the MySQL community, Mark Callaghan from Facebook will be giving another keynote, and we’ll also have Colin Charles from MariaDB.

It should be an exciting conference!

Percona Server to ship with jemalloc
+0 Vote Up -0Vote Down

Joseph Scott pointed me to a little tidbit hidden in the latest Percona Server release notes: “Percona Server for MySQL will now be shipped with the libjemalloc library.”  Percona published the results of some testing of various malloc libraries on their MySQL High Performance Blog last year, and it looks like this will have a very positive impact on performance.

I’m currently using MariaDB, so I’m hoping they pick up this change as well.

How to identify an unnecessary index
+0 Vote Up -0Vote Down

Let’s look at the index from the wp_posts table in a standard WordPress installation.

SHOW KEYS FROM wp_posts;
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts | 0 | PRIMARY | 1 | ID | A | 1772 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_name | 1 |




  [Read more...]
Big Data for Genomic Sequencing. Interview with Thibault de Malliard.
+0 Vote Up -0Vote Down
“Working with empirical genomic data and modern computational models, the laboratory addresses questions relevant to how genetics and the environment influence the frequency and severity of diseases in human populations” –Thibault de Malliard. Big Data for Genomic Sequencing. On this subject, I have interviewed Thibault de Malliard, researcher at the University of Montreal’s Philip Awadalla [...]
A Visual Guide to the MySQL Performance Schema
Employee +1 Vote Up -0Vote Down

If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or

  [Read more...]
Interesting MySQL optimizer case
+0 Vote Up -0Vote Down

Jaime at the MySQL Performance Blog had an interesting post regarding some unexpected behavior from the MySQL optimizer.  Although this particular case probably doesn’t affect most people, it does give some insight into how the optimizer works, and how subtle changes to a query can change performance.

Asynchronous MySQL queries in PHP
+0 Vote Up -0Vote Down

I was made aware today by a post on the MySQL Performance Blog that the mysqlnd driver for PHP has support for asynchronous PHP queries.  At the Midwest PHP Conference, I gave a talk on database optimization for web developers, and in a follow-up discussion with an attendee, I apparently misinformed him.

Midwest PHP Presentation Notes
+0 Vote Up -0Vote Down

Thanks for attending my presentation at the Midwest PHP conference!

Slides as PDF
Leave feedback on joind.in

Useful links:


  [Read more...]
FOSDEM 2013 Slides are available on [Plus]
+0 Vote Up -0Vote Down

I was at FOSDEM for the first time this year and it was very exciting.
I was very glad to see Percona, MariaDB and Oracle together.
If you’ve never been at FOSDEM, I recommend you to join us next year, this is truly a magical event!

You can find some of the slides on [Plus], enjoy! :

If you would like to add your slides, post a comment with the link.
And remember to visit this page for more slides about MySQL : http://www.mysqlplus.net/mysql-docs/




  [Read more...]
Changing the Size of the InnoDB Log Files In MySQL 5.6
Employee_Team +6 Vote Up -0Vote Down

In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

innodb $ ls -1s ib_logfile*
2096132 ib_logfile0
2096144 ib_logfile1

Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit

  [Read more...]
MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines
+4 Vote Up -0Vote Down

In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

I am going to create some virtual machines instead of using individual servers. But, you can

  [Read more...]
MariaDB 10.0: Named dynamic columns, now with documentation
+3 Vote Up -0Vote Down

During the last year, I was asked a number of times when/if MariaDB will support identifying dynamic columns by names instead of numbers. I am happy to say that named Dynamic Columns feature is in MariaDB 10.0.1 release. Now, after some literary effort, here is updated documentation: Dynamic Columns in MariaDB.

MySQL-State of the Union. Interview with Tomas Ulin.
+8 Vote Up -0Vote Down
“With MySQL 5.6, developers can now commingle the “best of both worlds” with fast key-value look up operations and complex SQL queries to meet user and application specific requirements” –Tomas Ulin. On February 5, 2013, Oracle announced the general availability of MySQL 5.6. I have interviewed Tomas Ulin, Vice President for the MySQL Engineering team [...]
MySQL 5.6 Windows MSI Installer
Employee +2 Vote Up -0Vote Down

First conference of the year is always a bit of a thrash for me. This time I have and RMOUG Training Days back to back. So I went to upgrade MySQL on my presentation laptop and got a pleasant surprise.

The MySQL 5.6 upgrade included not only the server but connectors and workbench

I had planned to install 5.6, the connectors, the docs, and Workbench separately as I packed. Low and behold. the upgrade did all the work for me. Very cool!

Except the packing. That I still got to do! See you in Miami and Denver.



  [Read more...]
Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication
+4 Vote Up -0Vote Down

In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as

  [Read more...]
No, Sheeri, MySQL 5.6 does not optimize subqueries away
+21 Vote Up -10Vote Down

Sheeri wrote a blog post that claims that “IN Subqueries in MySQL 5.6 Are Optimized Away” and uses that as a basis to conclude that subquery optimizations in MySQL 5.6 are superior to MariaDB’s.
The claim is incorrect (and so is the conclusion). As a person who has coded both of the mentioned FirstMatch and semi-join materialization, I think I need to write about this.

Sheeri wrote:

  • “So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery”
  • “In MySQL 5.6, the subquery is actually optimized away”
  • The first statement is somewhat true. The second one is not. I’ll


      [Read more...]
    The Data Day, Two days: January 25/28 2013
    +0 Vote Up -0Vote Down

    Informatica’s revenue is in. Hortonworks joins OpenStack. And more.

    For 451 clients: Jitterbit gives integration stack makeover, continues to mine salesforce.com bit.ly/VJrwPE By Krishna Roy

    — Matt Aslett (@maslett) January 28, 2013

    For 451 clients: Enterprise cloud provider Skytap offers new service based on Cloudera Hadoop bit.ly/UpnXij By @rachelchalmers

    — Matt Aslett (@maslett) January 25, 2013

    For 451 clients: Alteryx plots course for 2013 as it seeks to bring business users into

      [Read more...]
    MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part Two
    +4 Vote Up -0Vote Down

    This post is part two of MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves. In this post, I will be showing you how to use the MySQL Utility Script mysqlreplicate to create a new replication slave off a master database.

    This is also the fourth in a series that I will be doing on MySQL

      [Read more...]
    Fatal Half-measures in Incident Response
    +1 Vote Up -1Vote Down

    CSO Online writes about a rather sad list of security breaches at http://www.csoonline.com/article/721151/fatal-half-measures-in-incident-response, and the half-hearted approach companies take in dealing with the security on their networks and websites.

    What I find most embarrassing is that it appears (judging by the actions) that many companies have their lawyers do some kind of borked risk assessment , and decide that they can just leave things as-is and yell foul when there’s a breach. After all, particularly in the US prosecutors are very heavy handed with breaches, even when the company has been totally negligent. That’s weird, because an insurance company wouldn’t pay out for a break-in when you’ve left your front door wide open! The problem is of

      [Read more...]
    The Data Day, A few days: January 17-22 2013
    +0 Vote Up -0Vote Down

    DataStax and VoltDB launch their version 3.0s. And more

    For 451 Research clients: DataStax adds security and manageability to distributed NoSQL database bit.ly/Vb1IiT

    — Matt Aslett (@maslett) January 18, 2013

    For 451 clients: LogiXML serves up fresh cut of BI stack with an eye to more embedded analytics deals bit.ly/SppzsH By Krishna Roy

    — Matt Aslett (@maslett) January 21, 2013

    Inside H-P’s Missed Chance To Avoid a Disastrous Deal – WSJ.com on.wsj.com/Td7wrh via @

      [Read more...]
    MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part One
    +3 Vote Up -1Vote Down

    One of my favorite features of MySQL (http://mysql.com) is replication. Replication provides you with the ability to have MySQL automatically copy data from one MySQL instance to another. There are many benefits to using replication, but I just like having an extra copy of my data on another server in case the main server crashes. But if the master crashes, I can then use the MySQL mysqlfailover script to automatically failover from the master to the slave. (see my earlier post – Using the MySQL Script mysqlfailover for Automatic Failover

      [Read more...]
    The Data Day, Two days: January 15/16 2013
    +0 Vote Up -0Vote Down

    Funding for Ayasdi and Zettaset. NuoDB launches cloud database. And more

    For 451 Research clients: NuoDB launches distributed ‘cloud data management system’ bit.ly/UO3ssM

    — Matt Aslett (@maslett) January 15, 2013

    For 451 clients: Armed with $20m series C, Lattice Engines looks to bring sales intelligence inside bit.ly/11z4VdF By Krishna Roy

    — Matt Aslett (@maslett) January 16, 2013

    Ayasdi Launches with $10 Million from Khosla Ventures and FLOODGATE. bit.ly/X7oemJ

    — Matt Aslett (@maslett)

      [Read more...]
    Retrieving List of MySQL Users and Grants with Perl
    +0 Vote Up -0Vote Down

    Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.

    When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.

    But there were times when I

      [Read more...]
    MySQL Replication – Multi-Threaded Slaves (Parallel Event Execution)
    +3 Vote Up -1Vote Down

    If you aren’t familiar with MySQL replication, “Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default – slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.” (From: http://dev.mysql.com/doc/refman/5.5/en/replication.html).

    I use MySQL replication on my home office server. I don’t really have much data to store, but it

      [Read more...]
    MySQL@FOSDEM 2013: Call for papers closes in a few days!
    +2 Vote Up -0Vote Down

    This is just a reminder: Call for papers for MySQL & Friends devroom at FOSDEM 2013 is open until December 21st, which is this Friday. Please hurry up and submit your proposal. Possible topics include everything related to MySQL.

    New optimization in MariaDB 10.0: EXISTS-to-IN subquery rewrite
    +7 Vote Up -0Vote Down

    MariaDB 10.0 has got another new feature: Sanja Byelkin has pushed EXISTS-to-IN rewrite feature. It is an optimization targeted at EXISTS subqueries. The idea behind it is simple. EXISTS subqueries often have form:

    EXISTS (SELECT …  FROM … WHERE outer_col=inner_col AND inner_where)
    

    where outer_col=inner_col is the only place where the subquery has references to outside. In this case, the subquery can be converted into an uncorrelated IN:

    outer_col IN (SELECT inner_col FROM … WHERE inner_where)
    

    The conversion opens new opportunities for the optimizer. Correlated EXISTS subquery has only one execution strategy. Uncorrelated IN subquery has two:

  • re-run the subquery every time the subquery is evaluated (the same as in EXISTS)
  • Materialize the
  •   [Read more...]
    Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication
    +4 Vote Up -0Vote Down

    This post is the second in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts. You may want to read the first half of this post to understand how MySQL Workbench Utilities work and how you access the scripts. These scripts were written by Chuck Bell (a MySQL (http://mysql.com) employee)

      [Read more...]
    On Big Data, Analytics and Hadoop. Interview with Daniel Abadi.
    +0 Vote Up -0Vote Down
    “Some people even think that “Hadoop” and “Big Data” are synonymous (though this is an over-characterization). Unfortunately, Hadoop was designed based on a paper by Google in 2004 which was focused on use cases involving unstructured data (e.g. extracting words and phrases from Webpages in order to create Google’s Web index). Since it was not [...]
    Previous 30 Newer Entries Showing entries 121 to 150 of 858 Next 30 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.