Gabi Jack's Blog
  • About

Tag Archives: MySQL

In search of a faster query

Posted on June 14, 2015 by Posted in MySQL, Rails, Ruby

Recently, as I was fixing a minor bug in one of our applications at work, I had the opportunity to witness what a great difference a simple query can make.

The bug involved a very slow query, that was doing a join between a couple of tables that had grown too large in size. The query, in Rails, was as follows:

 

Model1.joins(:model2).order(:attribute1)


That query alone was taking nearly 2200 ms to complete,  kind of showing that it really is the devil to make a join between two large tables. The table for Model1, for instance, had nearly 90,000 rows and 10+ columns, while the table for Model2 was slightly smaller, with nearly 30,000 rows.

After examining the code, I realized we really only needed the id and attribute1 of Model1 records, so I modified the query slightly to retrieve only that:

 

Model1.select('attribute1, model1.id').joins(:model2).order(:attribute1)

 

Wow! This simple change brought the time down to around 520 ms, which was still sluggish, but a great improvement from the 2200 ms we began with.

It was a third iteration of the query, however, that  made the most impact. We had a third table, for Model3,  with an association to Model1  that we could use to our advantage. Although with no association between them, Model2 and Model3 both had a model1_id column, allowing us to do something like this:

 

model1_ids = Model3.joins('INNER JOIN model2 on model2.model1_id = model3.model1_id')
                   .uniq.pluck('model3.model1_id')
Model1.where(id: model1_ids).order(:attribute1)

 

That last query brought the time down to around 40 ms. Not bad at all!

I felt very pleased by this results, mostly because I learned  it pays to play around with different ways to do the same, and to keep an eye on the query times reported in the console. It’s also worth thinking about how large the tables are likely to grow, although I know it’s hard to predict the future. It is possible when this part of the application was written, some time ago, that first query used to be fast enough, most likely because the table was small too.

 

 

MySQL rails ruby

Quick Tip: MySQL Subqueries

Posted on December 30, 2013 by Posted in MySQL, Quick Tip

This is a little tip that a co-worker shared with me some time ago and has made my life so much easier.  I am often faced with a situation very similar to this: let’s say we have a table called users_rooms that includes the columns  user, room, deleted, created and modified. I am required to insert new rows in this table where the values  of the room column for user 123 should match all the available ones for the same column for user 128.  This is something easy to do with subqueries, like this:

"insert into users_rooms (user, room, created, modified) (select 123, room, NOW(), NOW() from users_rooms where user = 128) 
on duplicate key update deleted = 0, modified = NOW()";

This query will effectively insert a series of rows where the user will be 123, created and modified will be the current time and the values for the room column will match those of  user 128 . Easy!

MySQL SQL subqueries

Monday Morning Rambling

Posted on June 4, 2012 by Posted in personal

Now that the semester is over and Summer is practically here, I have some time to slow down a bit and go back to review any extra material or information that wasn’t covered properly due to lack of time. I had thought about enrolling in a class or two during the Summer term, but the ones I wanted are not  offered, so I guess it’s time to hit the books on my own, while I continue to look for other opportunities.

Something I realized while working on the last school project is that I really need to polish my skills in HTML and CSS, and become fluent in Javascript. I know, I’ve heard that some Java programmers look down on Javascript for some strange reason, but it seems to me that it’s an incredibly useful tool for web development. I only used a couple of scripts for my last project, one for validating a form and another one for creating a block of text that seemed to slide with you as you scrolled up and down the page, remaining always in view. I’ve seen some other  really cool things done with Javascript  and I’m eager to learn.

For now, I’m reading Murach’s  HTML, XHTML, and CSS. I actually won the book a few months ago, during a little contest the publishers had on Twitter. Can you believe that!  It’s basic, yes, but full of solid and useful information. I’ve been using parts of it as needed while working on some of my projects, but I guess it doesn’t hurt to read the whole book.  Actually, I must confess I love Murach’s books. They are amazingly clear, full of examples and read fast too.  It was actually thanks to Murach’s  Servlets and JSP book that I was able to successfully code the classes I needed to establish the connection pool with a MySQL database for one of my projects, since the official  textbook for my class, Servlet and JSP (A Tutorial) by Budi Kurniawan, doesn’t offer enough detail about it.  Of course, I also helped myself with information from  Howard Hyde’s    Java Web Database Application Development,  Volume 1 . That’s another book that wouldn’t hurt reading from cover to cover.

I’ll be sharing more about the code, and the code itself, in future writings.

java murach MySQL
April 2021
S M T W T F S
« Dec    
 123
45678910
11121314151617
18192021222324
252627282930  

Recent Posts

  • TIL: Working with a D3-based chart library
  • Diary of a Junior Dev: The Joys of Building
  • In search of a faster query
  • Living la vida Ruby
  • I love peer reviews… I hate peer reviews

Recent Comments

    Categories

    CyberChimps

    CyberChimps

    Marketed By Neil Patel
    © Gabi Jack's Blog