How to optimize database queries?
The SQL Language seems easy to learn – the commands follow a simple syntax and don’t describe the specific algorithms used to retrieve data. The simplicity might, however, be deceptive. Not all the database functions operate with the same efficiency. Two very similar queries can vary significantly in terms of the computation time. This article presents some of the best practices that can greatly boost your SQL queries.
1. Learn How to Create Indexes Properly
Learning how to index properly is the best thing you can do to improve the performance of your SQL queries. Indexes allow for quicker access to the database in typical situations. Database novices often find indexes mysterious or difficult. They either index nothing or they try to index everything. Of course, neither of these approaches is right. With no indexes at all, your queries are likely to be slow. If you index everything, your updates and insert triggers will be inefficient.
If you’re not sure that you know enough about indexes, take @MarkusWinand’s three minute-test. Markus’ website “Use the Index, Luke” is the best starting point to learn about indexes. He also published an excellent book on the subject.
2. Only Retrieve the Data You Really Need
A common way of retrieving the desired columns is to use the * symbol even though not all the columns are really needed:
SELECT * FROM users WHERE age > 20;
If the table is small, retrieving additional columns won’t make much of a difference. For larger data sets, however, specifying the columns might save a lot of computation time:
SELECT id, last_name, sex, age FROM users WHERE age > 20;
Keep in mind, however, that many popular ORM will not let you to create a query which selects only a subset of table columns.
Similarly, if you only need a limited number of rows you should use the LIMIT clause (or your database’s equivalent). Take a look at the following code:
SELECT name, price FROM products;
For instance, if you only want to display the first 10 records out of 50, 000 on your website, it is advisable to inform the database about it. This way, the database will stop the search after finding 10 rows rather than scan the whole table:
SELECT name, price FROM products LIMIT 10;
The LIMIT statement is available in MySQL and Postgresql, but other databases have ways to achieve a similar effect.
These above examples illustrate the general idea – you should always think whether you need all the rows returned by an SQL statement. If you don’t, there is always some room for improvement.
3. Avoid Functions on the Left Hand-Side of the Operator
Functions are a handy way to provide complex tasks and they can be used both in the SELECT clause and in the WHERE clause. Nevertheless, their application in WHERE clauses may result in major performance issues. Take a look at the following example:
Even if there is an index on the appointment_date column in the table users, the query will still need to perform a full table scan. This is because we use the DATEDIFF function on the column appointment_date. The output of the function is evaluated at run time, so the server has to visit all the rows in the table to retrieve the necessary data. To enhance performance, the following change can be made:
This time, we aren’t using any functions in the WHERE clause, so the system can utilize an index to seek the data more efficiently.
4. Consider Getting Rid of Correlated Subqueries
A correlated subquery is a subquery which depends on the outer query. It uses the data obtained from the outer query in its WHERE clause. Suppose you want to list all users who have made a donation. You could retrieve the data with the following code:
SELECT user_id, last_name FROM users WHERE EXISTS (SELECT * FROM donationuser WHERE donationuser.user_id = users.user_id);
In the above case, the subquery runs once for each row of the main query, thus causing possible inefficiency. Instead, we can apply a join:
SELECT DISTINCT users.user_id FROM users INNER JOIN donationuser ON users.user_id = donationuser.user_id;
If there are millions of users in the database, the statement with the correlated subquery will most likely be less efficient than the INNER JOIN because it needs to run millions of times. But if you were to look for donations made by a single user, the correlated subquery might not be a bad idea. As a rule of thumb, if you look for many or most of the rows, try to avoid using correlated subqueries. Keep in mind, however, that using correlated subqueries might be inevitable in some cases.
5. Avoid Wildcard Characters at the Beginning of a LIKE Pattern
Whenever possible, avoid using the LIKE pattern in the following way:
SELECT * FROM users WHERE name LIKE '%bar%';
The use of the % wildcard at the beginning of the LIKE pattern will prevent the database from using a suitable index if such exists. Since the system doesn’t know what the beginning of the name column is, it will have to perform a full table scan anyway. In many cases, this may slow the query execution. If the query can be rewritten in the following way:
SELECT * FROM users WHERE name LIKE 'bar%';
then the performance may be enhanced. You should always consider whether a wildcard character at the beginning is really essential.
Bonus Tip – Read the Execution Plan
The performance of your SQL queries depends on multiple factors, including your database model, the indexes available and the kind of information you wish to retrieve. The best way to keep track of what’s happening with your queries is to analyse the execution plan produced by the optimizer. You can use it to experiment and find the best solution for your statements. If you use PostgreSQL, the article about execution plans on our blog may be of interest to you.
Tips when writiing poem what does m and m stand for Tips on how to exercise when you're in chronic pain What to do about brown tips on plants Youtube cycling tips how the week Best tips for when to pump gas How to get tips hosting osrs Who said dogs can't do tricks in the hood How to crack your back? what does soviet mean what does declined mean what does merry christmas mean How to install minecraft forge? How to make a music video tips What is wages salaries tips etc What other cute facebook tricks are available besides the one when clicking on the word congrats How to watch mare of easttown what does helicase do How to play gin rummy How to make a floating shelf? what does past tense mean what does amp hours mean How to connect airpods pro? How to count heart rate How to use neti pot what does lgbtqia stand for How are carbenaro effect tricks dont What is medicare wages and tips on w2 what does keywords mean How much does it cost to euthanize a dog? What is that dog doing tricks with owner How to get wet what does a clitoris look like what time does kazuya come out How to wear joggers fashionably what are the roles of the president How to sell on amazon for beginners What are important tips to know about greece what time does abc store close How much do waiters make an hour with tips idaho How to pronounce epoch What kind of person tricks pregnancy How to make tuna sandwich How to make yourself vomit? Linkedin profile tips when writing your job duties How to do tricks with bike in pugb What kind of revolver does western use to do tricks what does relegated mean what does the word manifest mean what does fondling mean what does lcm mean in math How to block messages on iphone? How to do tricks on mount in world of warcraft what does an mri of the neck show When she tricks you but you're pregnant How to screenshot on hp laptop what time does the movie theater open what dies 222 mean How to apply nail tips correctly 15 genius tricks for amazing dinner parties—from real people who did them. How to sketch? How to identify delta shower faucet model? what does bbg mean on roblox what does watermelon do for the body How to increase sex drive in women what are above the line deductions what does four score mean what does binturi mean what does lemon water do How to slide in madden 22 How to wear n95 mask? 6mwe what does it mean How to have sexs? How to get rid of diaper rash what does low anion gap mean How to identify skateboarding tricks what does it mean to straddle someone Why do.people think magic tricks are.for.kidd How long does it take to learn how to drive? Tricks to do with your tongue when sucking How is a delivery personpaid on tips from credit card charges what does wr mean what does bi weekly pay mean what does vibes mean How to get cheap flights What do brits think of pg tips what does lick mean what does a red bracelet mean How much does it cost to paint a car what does furious mean what does direct hire mean what are lawn grubs How to find someone's phone number? what does whimsical mean How to vacuum above ground pool? How to do tricks with nic what does carat mean in diamond what does traumatized mean what does dp mean what does entanglement mean what does tribute mean what does heritage mean what does punani mean How to teach a australian shepherd tricks what color are kidney stones How to clean a keurig with vinegar? what does wby what does the number 9 mean in the bible How to make cum taste better? How to determine medicare wages and tips How to get rid of stretch marks hoe tips what does pup mean in football How to take contacts out with long nails How to deepen your voice? what does curious mean How to start a youtube channel How to add signature in word? How hilary tricks voters what does adjoining mean Where to watch how the grinch stole christmas Tips on how to make your penis bigger what does being doxxed mean What are some cool magic tricks of overcoming odds Why do my finger tips split How to get snap score up Tricks when you get balayage what does a red heart emoji mean What jobs give good tips How did shin lim do the tricks on agt How to blur background in zoom? How to invest money to make money? How many hat tricks does mitch marner have How to drive on black ice safety tips what does her stand for What is the legal serving wage in philadelphia before tips? How long to cook chicken drumsticks in oven How to teach a ball pytho tricks How to cancel fitbit premium How to get rid of smelly feet permanently? How to stretch shoulder blade what does monotheistic mean Tips to getting a lot of followers when streaming facebook what does kate mean what does tribal tattoo mean what are nuns what does idf stand for How much quantity of eat 1 year baby tips in urdu what does salvage title mean what does it mean when you cry for no reason How to catch a leprechaun read aloud? How to let go of anger Dai tips on wicked eyes and wicked hearts how to triger morgan what does retention mean Shopping cart hero 2 controls how to do tricks How to apply for doordash Tips on how to purge stuff in my home what does closure mean How to remove gelish soft gel tips How to clear history on chrome? How to make a green tea shot? How to make earbud tips out of ear plugs How to find anyone's email: 13 little-known tricks Who do you need cash tips for on a disney cruise What do people need to view guitar tricks Tips on how to jump higher How to get rid of sunburn redness overnight what does the number 1212 mean Who do you give holiday tips to what are secondary colors what time does the metro close Tips for sore breasts when breastfeeding How to decrease red blood cells Tips and tricks when filling out fafsa How to teach a chicken tricks what does adele look like now How to make rib tips in the crock pot what does upsell mean Tips on how to attract a capricorn woman How to get your ex boyfriend back How to email facebook what does edm stand for what does smh mean in text How are card tricks done article what does glib mean How to pronounce ubiquitous Tricks for how to remove a crankshaft sensor on a saturn ion How to stop snoring naturally Tips when getting chemo How to get better tips door dash Tips on how to fix loose glasses what does paramore mean How to build cornhole boards what does seeing a black cat mean How to get weed out of system How to get guests to leave tips at a bed and.breakfast How to win mega millions tricks How to get to sleep when you can't what does asl mean How to share google calendar on iphone what does lizard poop look like How to use tricks on shopping cart hero 2 How to make fresh green beans? what does supinate mean what does rejuvenate mean How to cook pork roast? what does were mean what states does ipass work in How to screen record with sound? Persuasive speech about how tobacco indusrty tricks us How to do beginner tricks on your trick board what does a braxton hicks contraction feel like How conservative media tricks what does the celtic cross mean what are puebes what does the bible say about rh negative blood what does dry humping mean How to induce labor naturally? How long to cook potatoes in oven? How to care for roses? How to be gay? Why do the tips of my cotton in an rda stay moist but around the coil dries out How to clean bathtub How to turn off airpods How to propagate lavender How to put air in tires what does low hct mean How to cough up mucus? what does cell mean what does coinsurance mean after deductible How long does it take toget sns with tips? what does perennial mean How to turn on hotspot on iphone What do vertical wing tips do what does the supremacy clause do? How to calculate gross annual income? what does manifestation mean How to be single tips How to make nail tips what are the best dirt bike brands How to sharpen a serrated knife? what does a type a personality mean what does niger mean Tips for going to the range when it's extremely cold out what does rendezvous mean what does tsp mean what does pardon mean for president How to use a paint sprayer How to know if eggs are bad? what does spectator mean How to do tricks atv vs mx alive How much does it cost to rebuild a transmission The three-step process of laser hair removal, and tips when getting the treatment yourself. How to how to train how to teach rats to do tricks what does racism mean Pokemon ultra moon how to unlock new tricks How to do cool tail with tricks a skateboard How to get roblox voice chat? Magic tricks how to make a card disappear what does the name ann mean what are pork rinds made of How to build a bridge in animal crossing? what does pathogen mean How many hat tricks has messi scored against real madrid what are the 10 commandments and what do they mean How to cook stew meat? what does sticky discharge mean what does subscribed mean on snapchat what does cop mean