Annyce Davis

Helping you write beautiful code

  • Home
  • Public Speaking
  • About Me
  • Courses
  • Life

Improve Performance of Grails Queries in MySQL

January 19, 2010 by Annyce Davis Leave a Comment

A web application that I was developing would become extremely slow as the number of requests increased per minute. I couldn’t figure out why because with my local database everything seemed fine. Here is the process that I used in MySQL to determine the bottleneck in my database. The final problem was a missing index on a table that was used in the where clause of a select statement over and over again. I included a snapshot the MySQL Process list at the end of the post. Hope this helps someone.

Steps to Follow:

  • show full processlist;
    • shows each database connection that has been allocated for your database
    • contains the time a connection has been Sleep
    • contains how long a Query is taking to execute, you want this to be less than a second ideally
  • describe <query>;
    • this gives you the database’s plan for executing the query
    • you want the number of rows that will be looked at to be as small as possible
  • show index from <table>;
    • this will list all indices contained on a table
    • this is a quick way to discover if you are missing a key index that could improve query performance
  • create index <tablename_fieldname_idx> on <table>(field);
    • this will add a new index to a table

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)

Related

Filed Under: Grails Tagged With: MySQL, Performance

Newsletter

Start a new tech group on Meetup

Follow Me

Dev Machine Setup – Mobile Development

A new machine. Now what? Time to set it all up. Inevitably I end up going back and forth from my old machine or worse yet, my memory, to figure out what needs … [Read More...]

Dagger Multibinding in Dynamic Feature Modules

We're adding the use of dynamic feature modules to our app. This will help us to improve build speeds, conditionally include features and decouple our code. As … [Read More...]

Categories

  • Android (49)
  • Career (2)
  • Communication (4)
  • Flutter (1)
  • Git (4)
  • Gradle (4)
  • Grails (23)
  • Java (8)
  • JavaScript (6)
  • Kotlin (11)
  • Life (4)
  • Public Speaking (22)
  • RxJava (1)
  • Software Development (3)
  • Twitter (3)
  • Uncategorized (11)
  • Video Course (4)

Copyright © 2021 · Beautiful Pro Theme on Genesis Framework · WordPress · Log in