Annyce Davis

ones and zeros

  • 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)
  • More
  • Click to share on Mastodon (Opens in new window)

Related

Filed Under: Grails Tagged With: MySQL, Performance

Follow Me

Each year there's a new version of the Android SDK released. Along with the myriad of new features come deprecations. When something is deprecated, although … [Read More...]

I didn't intentionally aim to create a rhyming title, but there you have it. Each year, I look forward to Google I/O like a kid going back to school. This year … [Read More...]

Categories

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

Follow Me

Archives

Categories

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