Writing queries that are optimized

When writing a query there are basic parts:
1) the result set
2) the tables to get the data from
3) the where clause – provides filtering
4) group by for grouping data to be aggregated.

This article will discuss areas 2 and 3 since that’s where most query optimizations occur.

Joining Tables efficiently:

Mysql has a practical limit to the number of tables you should join.  It is 10 (some opinions differ).
While Mysql can join more than that but statement performance may suffer due to the calculation of the statement execution plan.  The more tables Mysql has to deal with, the greater number of plans it has to explore when deciding
on how to execute the statement.

When you join 2 tables you want to make sure the columns you join on are indexed in at least of of the tables.
If the columns are indexed, Mysql can find the row in the joined table quickly via an index lookup.

    Select xxx,yyy,xxx
    from table_a
    join table_b on table_b.indexed_column = table_a.indexed_column

In this example the column ‘indexed_column’ represents a column that has an index on it in 
table_a and/or table_b.

So for each row in table_a, the select will quickly find the matching row in table_b due to the join condition which is on the ‘indexed_column’.

Now if we join the tables on columns that are not indexed in either table_a or table_b.

    Select xxx,yyy,xxx
    from table_a
    join table_b on table_b.unindexed_column = table_a.unindexed_column

Mysql will have to perform a table scan of table_b for each row in table_a.
So if table_a has 100 rows in it and table_b has 1m rows that means that Mysql will have to scan table_b 100 times scanning a total of 100 million rows.
This is very inefficient.

Subselects as tables:

Mysql materializes subselects.  What that means is the first time the subselect is run, Mysql creates a temporary table to store the result set so Mysql does not have to re-execute subselect.  Note: if your result set is very large, say hundreds of thousands of rows, that can create a very large temp table.

Many times I have see queries that use a subselect as a table.  That is fine.
However you must remember that if you join a subselect to another table, the column used for the join condition must be indexed in the joined table.  
If the joined table does not have an index on the column used in the join condition then the joined table will be scanned for each row in the subselect result set.

That also means that you should not join one subselect to another subselect since the result sets of subselects are not indexed.

Another thing to keep in mind is that the columns used in join conditions must be the same datatype.  If they are not the same datatype and there is an index on the columns, Mysql can not make use of the indexes and will end up performing table scans.

Something else you need to know is that Mysql is pretty smart.  When it creates it’s join plan, it can detect which table should be the main table and which should be the joined table.
For example, if the join condition column is indexed in table_a but not in table_b, it will join table_a to table_b using table_b as the primary table.  It also means that Mysql may not join the tables in the same order you think it should.  I have seen situations where Mysql did not ‘guess’ correctly.
To override Mysql’s guess, you can specify ‘STRAIGHT_JOIN’ after the SELECT statement.  This will cause your tables to be joined in the order you specify. 
The primary table is where Mysql starts getting rows from first.

The ‘WHERE’ clause:

This is the most important part of an sql statement as it filters out rows.
If you don’t use or it is not used correctly, your select will be performing full tables scans.
This is very very bad.

Using a WHERE clause is easy.  You simply specify a column = another_column AND/OR a column = a CONSTANT.

Anytime you filter on a column the column should be indexed.
Also never change the datatype of the column you are filtering on (as in using CAST).  This prevents Mysql from using any associated index.
Mysql can only use an index when the column is used in it’s ‘native’ datatype.

The datatypes of the column you are filtering on and the constant/column you are comparing to must match.  If you need to change the datatype, change the datatype of the constant/column and not that of the indexed column.

Using an ‘IN’ clause:
Limit the size of the items inside an ‘IN’ clause.  If you have 50 items that’s fine. Don’t have 25 million.
There are better ways to filter.

Subselect in a WHERE clause:

Using a subselect in a WHERE clause is okay if the subselect returns a small result set.
If your subselect returns a very large result set, you may be better off simply joining the tables in the subselect like normal tables.

Get rid of subselects:

Due to the inability of subselects to be indexed you can get rid of them.
What you do is to create a temporary table.  If you specify ‘ENGINE=innodb’, the table will be created as an innodb table and will exist in the innodb buffer pool.  This avoids eating up the servers free RAM (reduces the chances of out of memory situation).  When you create this temporary table, you can create indexes.
Then, in your query you can join this temporary table just like a normal table on the indexed columns.
Maintaining the indexes (upon insert into the table) will cause a minor performance hit but the performance gain from having the indexes will more than offset that.

The ‘Takeaway’:

The takeaway from this document is that INDEXES are one of the best performance boosters.  Create them and use them.

You can not use any function (such as UPPER(), LOWER(), user_defined_function(), …..) on the indexed column or Mysql will not be able to use it.

Find and Drop a user across multiple database servers

If you ever wished to find a user and drop them from multiple servers. Here is a one-liner:

Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaDbUser -User User1 | Remove-DbaDbUser -Force
Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaLogin -Login User1 | Remove-DbaLogin

The first line of the code remove user from all the databases. The second line drops the login from each SQL instance.

This is especially useful when you need to drop a user when they leave the company.

MySQL Classes And Tutorials

These are online, self-guided ways for developers wishing to gain more RDBMS understanding (or have a refresher course) in the two most ubiquitous platforms — MySQL and PostgreSQL.  I add and remove from this list from time to time as material becomes outdated or better bits are found.

Disclaimer:  I didn’t have a hand in creating any of these, but have recommended them in one format or another for several years.

All links open in a new tab.


MySQL for Developers

MySQL in 7 Days

Learn MySQL via TutorialsPoint (quite good, complete)

Simple SQL Overview (beginners, from W3Schools)

PostgreSQL Tutorial (also has you install it on your platform of choice)


15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips


MySQL Tutorial for Beginners [Full Course] (Programming with Mosh)
https://www.youtube.com/watch?v=7S_tz1z_5bA – (3h 10m)

MySQL Tutorial For Beginners (Intellipaat)
https://www.youtube.com/watch?v=WmGgxTpGs_8 – (2h 5m)

Learn PostgreSQL Tutorial – Full Course for Beginners
https://www.youtube.com/watch?v=qw–VYLpxG4 – (4h 20m)

Postgres for Beginners (Simplilearn)
https://www.youtube.com/watch?v=eMIxuk0nOkU – (1h 30m)

Setting up an SSH tunnel

This howto page will provide instructions on how to reach services running inside a firewall from outside of the network by using the Putty SSH Client and SSH Port Tunneling.


Download Putty.exe.

Port Tunneling

Launch Putty. Different categories will be listed on the left side, click on Connection > SSH > Tunnels.

Under Add new forwarded port:, enter the following information:
Source port[port on local machine]
Destination[hostname of remote machine]:[port on remote machine]
Click Add.

It would look like this if I wanted to forward port 80 on the CCIS webserver to 8080 on my local machine:

Clicking Add will add it to the list of forwarded ports:


After setting up the port tunnel, select Session from the category list on the left side.
Enter login.ccs.neu.edu in the Host Name (or IP Address) field and click the Open button at the bottom right.

Select Yes if prompted with this window:

Use your CCIS username and password when prompted to login and your port tunnel will be setup.

Utilizing the Port Tunnel

Now that the port is tunneled, you can connect to it using localhost:[port forwarded] where [port forwarded] is the local port you chose earlier.

In our previous example we forwarded port 80 on http://www.ccs.neu.edu to localhost:8080. We can now open up a web browser and browse to localhost:8080 to see it:

MSSQL Over an SSH Tunnel

The steps are practically the same as tunneling any other service, except the port you will tunnel is 1433. When connecting from MSSQL Management Studio, the connection host will be,[port you forwarded] . Notice the comma between the ip and the port number, this is very important. The following screens will show the proper setup:

And there you have it, you should now be able to SSH Tunnel to any service inside a firewall.

Tomcat 5/6/7/8 – How To Install An SSL Certificate

Tomcat–not my favorite.

General Steps

  1. Create a Key and Certificate Request
  2. Issue the Certificate from your favorite Registrar
  3. Merge the Certificate into a Tomcat File

Create a Key and Certificate Request

On your favorite Linux or Windows box, make sure you have OpenSSL.

I am making a directory called /home/keystore.  Seems fitting.


mkdir /home/keystore

First we need a Private Key.  This is yours and yours alone.

So, the private key is critical.  It’s your unique identifier for this SSL cert.

Next, we need to generate the request to send to GoDaddy, InstantSSL, etc

Now, the command:

You are going to be prompted for all of the details as follows. For Wildcard, use *.mydomain.com. For other hosts, just use the hostname. ie mydomain.com (you will get www automatically)

Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Texas
Locality Name (eg, city) [Default City]:Tim
Organization Name (eg, company) [Default Company Ltd]:My Domain Inc
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:*.mydomain.com
Email Address []:support@mydomain.com

Press Enter on the Extra fields, no password needed.

Ok, once finished, take your CSR and submit to your provider. Once you submit, you wait and then you will get your certificate. You may have to check email to approve it.

Now the Easy Part!

Merge the Certificate into a Tomcat File

If you are a GoDaddy Customer, you will get two files. Other providers might send you on a wild goose chase for the Bundle file.

6e00664a60ac4578.crt  - This is the Actual Certificate
gd_bundle-g2-g1.crt   - This is your Bundle file with all the certificate chain data from GoDaddy

For simplicity and understanding, let’s rename the file:

mv 6e00664a60ac4578.crt mydomain.crt

Now, let’s make the Tomcat keystore container

openssl pkcs12 -export -chain -CAfile gd_bundle-g2-g1.crt -in mydomain.crt -inkey private.key -out keystore.tomcat -name tomcat -passout pass:changeit

Ok, you have everything you need. Now, setup Tomcat.

Installing the Certificate in Tomcat

Let’s copy the file to our tomcat installation configuration directory.  My tomcat was in /usr/local/tomcat5

cp keystore.tomcat /usr/local/tomcat5/conf

Now, we need to enable SSL.  So, we need to edit the server-wide server.xml file.  Find the section like this:

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<Connector port="8443"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />

Replace it.  Mine looks like this:

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<Connector port="443"
maxHttpHeaderSize="8192" maxThreads="250" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true" SSLEnabled="true"
clientAuth="false" sslProtocol="TLS" keyAlias="tomcat"
keystoreType="PKCS12" />

Lastly, find any other references to port 8443 in the server.xml file and replace them with just 443.

Now, restart Tomcat and enjoy your newly functioning wildcard certificate.

I hope this bridges the gaps on some of the other articles out there.

%d bloggers like this: