SELECT Queries - Advanced CRUD explanation part 2

CRUD queries are one of the cornerstones of every database – reading (SELECT) queries help us read data and they’re absolutely crucial to any application. Figure out how they work with us!

---

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client

---

Preface

In the first part of the series about CRUD queries, we have walked you through the queries that help us INSERT data into our database into our database instances. In this part, we’re walking you through the queries that read it – SELECT queries.

SELECT queries are necessary for any application – be it web-based or not. Most of the time when our data is being read we don’t even think about the process, but our databases are quietly churning our SELECT queries. Here’s what you need to know about them.

SELECT Queries – the Basics

In many cases, a SELECT query looks like this: CRUD-queries are one of the cornerstones of every database, and INSERT-queries are the first ones within the list. Learn everything about them in this article.

A SELECT Query Example.

In most cases, SELECT queries also have a WHERE clause that specifies what exactly to search for and with the WHERE clause they look like this:

1 SELECT * FROM table_name WHERE column = ‘value’;

To dig further into them, let’s enable profiling and look into what we have. First, we run our query with profiling enabled:

Profiling a SELECT query - first step

Once that’s done, we need to figure out the ID of the query:

The ID of the SELECT query.

And finally, we profile the query itself:

Profiling a SELECT query.

The results of the profiler are all the same – those who have read the first blog of these series will remember what each of those mean, but if you don’t, we’ve also provided the same explanations below:

1. starting – the query starts and there’s nothing much to explain here.
2. checking permissions – the database checks whether there are sufficient permissions for the query to be executed. If there are not, the query stops here and provides an error.
3. opening tables – tables need to be open for any queries to have any effect on them.
4. init – the query is initializing its processes.
5. system lock – the database is checking whether there are any locks on the database.
6. update – the query updates data (in our case, inserts data.)
7. end – the query is closing its processes and coming to an end.
8. query end – the query itself stops here (end refers to the database closing processes necessary for it to run, but not the query itself.)
9. closing tables – the database is closing all tables that were affected by this query.
10. cleaning up – the database is cleaning up and preparing for the next query to be run.

Contrary to INSERTs, partitions and indexes help SELECTs be faster as they help them read through less data.

As you can probably tell, there are specific things you will need to consider when working with SELECT queries as well. Let’s dig into those.

The Specifics of SELECT Queries

To speed up SELECT queries, follow these tips:

• Index the columns you’re searching through – if the columns you’re searching through (columns after a WHERE clause) are indexed, your SELECT queries will be faster due to the fact that they’ll read through less data. There’s much more to indexes, though – use ordinary (B-Tree) indexes if you don’t need any specific features from them, and if you need to use wildcards or the power of full-text search features, you might want to look into FULLTEXT indexes too. To explain indexing by itself we’d need an entire book though, so for those who are interested, we suggest you read Relational Database Index Design and The Optimizers by Tapio Lahdenmaki and Mike Leach.

• EXPLAIN your queries – using the EXPLAIN query will tell you whether your indexes or partitions are actually used by the database. Everything’s simple – insert an EXPLAIN keyword at the beginning of your query and you’re done:
  
  
  

  EXPLAINing a Query.

• Make use of partitions – the more partitions you have, the more space on the disk your table will occupy, however, the faster your SELECT queries will be as well.

• Select as little data as possible – everything’s simple. SELECT column will likely be much faster than selecting everything (SELECT *) – do you agree? Only select data that’s absolutely necessary.

• Avoid wildcards at the beginning of a search string wherever possible – using a wildcard will likely slow the query down a little, but using a wildcard at the beginning of a search string will tell your database “I’m searching for anything before a certain string.” If you’re searching for anything, the search will likely be slower than if you would say “I’m searching for a string that begins with ABC and then ends with something I don’t know.” That means instead of queries like this:
  
  
  

  Wildcards in a SELECT query.

  
  
  Write them like so:
  
  
  

  No wildcard at the beginning of the search string.

  
  
  Finally, make sure to employ a proper SQL client such as the one built by DbVisualizer when making use of these tips – while SQL clients themselves won’t solve the issues related to your queries, the power of SQL clients cannot be understated. Did we tell you that DbVisualizer is the one SQL client with the highest user satisfaction? It’s used by tech giants such as Netflix, Google, and Meta, as well as other companies such as Tesla and NASA (yes, the one that works on space), and if that doesn’t tell you enough, simply have a look through its features and make your own decision.

Summary

In this blog, we have walked you through the power of SELECT queries – another necessary component in the CRUD area. These queries read through data and comprise much of the work for databases across the globe.

Now that you’ve finished with this blog, make sure to explore other blogs in these series and come back to our blog to learn more in the future!

FAQs

How do I speed up SELECT queries?

SELECT queries can be made faster by making them read through less data. That’s all there is to it – employ any technique (SELECT a column instead of selecting everything, employ indexes or partitions) – the result will still be the same. If your queries read through little data, they will be fast.

Why do indexes and partitions make SELECT queries faster?

They make SELECT queries faster because they let these queries read through less data.

Why should I use a SQL client?

SQL clients help you write SQL queries with ease and ensure your database performance, availability, and security by providing multiple features that solve the most pressing problems for developers and DBAs.

Now, make use of DbVisualizer – the SQL client with the most user satisfaction. Did we tell you its premium features can be evaluated for free?

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.