Things to Watch When Writing SQL — How to Write Efficient SQL Statements

The blog hasn’t been updated for a while, because I was changing jobs recently, moving from Shijiazhuang to Beijing — a cross-province move, plus job interviews and so on. Now things have finally stabilized, so I can write something to share.

This job change fits my expectations fairly well — a rather special position, in Development Department 2, the second-tier development team. Although I’m not developing the main product, I do develop some peripheral system modules, and I also have Ops tasks. The Ops tasks are even more fun: not only can I see the entire system’s source code, I can also touch the database and even the servers, and I’ve learned a lot of things I hadn’t considered before. Today I’d like to share some experience I’ve recently learned or realized — some notes and experience on writing SQL queries.

Before, when I wrote SQL, I was rather casual and didn’t care much — as long as it worked. But after getting into Ops, Teacher Zhao told me a lot, and emphasized that you can’t use IN or NOT IN in SQL. In interviews at other companies I was also asked about creating indexes on databases. So today let’s look at indexes and notes on writing SQL.

The main idea is to make use of indexes as much as possible to speed up queries, but some writing styles make the data engine give up using indexes, so pay attention to the writing:

1. Avoid using != or <> operator in the where clause, otherwise the engine will give up using the index and do a full table scan.
2. Avoid using or to connect conditions in the where clause, otherwise the engine will give up using the index and do a full table scan. E.g.: select id from t where num=10 or num=20 can be written as: select id from t where num=10 union all select id from t where num=20.
3. Use in and not in with caution too, otherwise it leads to a full table scan. E.g.: select id from t where num in(1,2,3). For consecutive values, use between instead of in: select id from t where num between 1 and 3.
4. The following query also leads to a full table scan: select id from t where name like '%abc%'.
5. Using a parameter in the where clause also leads to a full table scan. Because SQL only parses local variables at runtime, but the optimizer cannot defer the choice of access plan to runtime; it must choose at compile time. However, when building the access plan at compile time, the variable’s value is still unknown, so it cannot be used as input for index selection. The following statement will do a full table scan: select id from t where num=@num. It can be changed to force the query to use an index: select id from t with(index(indexname)) where num=@num.
6. Avoid expression operations on a field in the where clause, which will cause the engine to give up using the index and do a full table scan. E.g.: select id from t where num/2=100 should be changed to: select id from t where num=100*2.
7. Avoid function operations on a field in the where clause, which will cause the engine to give up using the index and do a full table scan. E.g.: select id from t where substring(name,1,3)='abc' — ids starting with abc. select id from t where datediff(day,createdate,'2005-11-30')=0 — ids generated on ‘2005-11-30’. Should be changed to: select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'.
8. Don’t perform functions, arithmetic operations, or other expression operations on the left side of ”=” in the where clause, otherwise the system may not be able to use the index correctly.
9. Don’t use select * from t anywhere; use a specific field list instead of ”*”, and don’t return fields you don’t need.
10. Avoid judging a field for null in the where clause, because to know whether it’s null you have to scan the whole table, so the database engine will automatically give up the index and do a full scan.
11. First consider creating indexes on the columns involved in where and order by.
12. Not all indexes are effective for queries. SQL optimizes queries based on the data in the table. When an indexed column has a lot of duplicate data, the query may not use the index — e.g. a gender column, which is only male or female; then what’s the point of the index?
13. More indexes isn’t always better. Indexes do improve the efficiency of corresponding select statements, but they also reduce the efficiency of insert and update, because inserting or updating may rebuild the index. So at most 6 fields with indexes is enough; more will backfire.
14. Avoid updating indexed data columns as much as possible, because the order of an indexed data column is the physical storage order of the table records. Once that column’s value changes it will adjust the order of the entire table’s records, consuming considerable resources.
15. Use numeric fields as much as possible. If a field contains only numeric information, don’t design it as character type, which reduces query and join performance and increases storage overhead. This is because the engine compares every character in the string one by one when processing queries and joins, while for numeric types it only needs to compare once.
16. When creating a temporary table, if you insert a large amount of data at once, you can use select into instead of create table to avoid generating a lot of log and improve speed; if the data volume is small, to ease the system-table resources, you should create table first, then insert.