Must change item for configuring MySQL for Windows

I recently installed MySQL and the MySQL workbench on my Windows 10 PC. It’s working great. I loaded up about 2 GB of data in two 3 tables with roughly 2M rows total. Most of my simple (single table by ID or Full Text Index) queries ran really fast, and I was happy.

Then, I started working on queries that join tables. They were slow – like 26 seconds slow for a two table join by integer ID using a FK (simple stuff).

First, I checked my indices. I added several indices just trying things and hoping it’d work. That did not solve the slowness.

I had to tinker in Workbench quite a bit imagining what could be wrong and how do I identify the source problem, which turned out to be good learning exercise.

Eventually, I saw (on the Workbench Server’s dashboard) that InnoDB buffer usage went to 100% during my long running queries. I didn’t really know what that meant, but I was out of “good” ideas to try so I was happy to have any leads.

I asked the internet sages (search engines) about this “InnoDB buffer usage at 100%”, and I found some leads. It turns out that buffer size is set in “my.ini” (on Windows) or “my.cnf” (Not-windows). The default was 8MB, which is amazingly low for any modern computer. (MySQL on my phone would want a bigger value than that as a default.)

I found my.ini at this path on my Windows 10 PC:

C:\programdata\MySQL\MySQL Server 5.7\my.ini

Specifically, edit that file with notepad or notepad++. Look for this value:

innodb_buffer_pool_size=8M

I changed mine to 1G first, and now I’m using 2G. I don’t even think that’s a lot, but this is all on my dev-box so I don’t want to give it all of my RAM.

innodb_buffer_pool_size=2G

After you change that my.ini value, be sure to restart your MySQL service. 

  1. Type services.msc in to the Run box on Windows to bring up your services manager. 
  2. Find the MySQL service, and right click it. 
  3. There should be a “restart” option, so click that.

After this change, my 26 second simple table join query went to sub-second time.