End Your If

Category: SQL

  • Load Testing Mysql with mysqlslap

    I have a few thoughts for some testing/comparison of mysql tables that I have been curious about.  Before starting though, I thought it was prudent that I should give it some thought about the approach.  The first thing that came to mind was altering the A Simple But Effective Speed Comparison code to execute a bunch of SQL queries against my local installation.

    I really didn’t like this approach because of the potential inaccuracy due to the overhead of the language and connecting to mysql before executing the queries.  The whole purpose of these potential upcoming articles is to compare mysql, not a specific language integrating with mysql.

    Luckily for us, as of Mysql 5.1.4, a tool called mysqlslap is shipped with the server installation.  This tool allows us to client emulation against our mysql server!

    (more…)

  • Update: It Actually Should Be LEPP Not LEMP or LAMP!

    In several recent posts: It’s LEMP not LAMP! and Amazon EC2 Shoutout! I’ve discussed how I switched from a shared hosting account with GoDaddy to a dedicated Micro instance with Amazon.  At the same time I decided to go out and try nginx (pronounced Engine-X) and remove Apache from the equation.

    This saw some incredible results.  However, several times since switching I’ve run into several database issues where it appears Mysql runs out of memory!  Performing a top command shows over 15 mysql processes running at anyone giving time each taking up to 10% of the memory.  On a micro instance I only get 600mb, so this adds up quickly!

    (more…)

  • Understanding group bys

    Yesterday, at work, it occurred to me, not for the first time, that something that comes so easily to me, does not to others.  I had tasked someone with, what seemed a simple task, to retrieve a list of users who have reached minimum payout.

    The payouts were stored in a separate table from the users and users could have multiple payment records prior to reaching payout, thus we would need to use a SUM() on the amount.  Because we also required other data, we need to use a group by to properly sum the amounts a user has.

    The task was handed back to me several hours later to review, it was quite evident that the main concept was “grasped”, but the understanding of group bys was not. (more…)

  • CakePHP Containable Statement Pitfalls

    On some of our more recent projects, we have been using the Containable behavior that CakePHP provides.  It works quite nicely, a lot easier than the previous format of having to bind and unbind models on the fly.

    The pitfalls began to appear when some of our clients were reporting that some pages are extremely slow.  After a bit of research, it was quite apparent that the root of the cause was attributed to us using the containable feature. (more…)

  • The Importance of Database Indexing

    If you are not familiar with database indexes or even if you are, but not sure when to use them, this article will be perfect for you.

    In this article, I will show you an example of a simple query without an index and then that same query again with the index.  I am certain that you will be shocked by the difference.

    (more…)