Login
Password

Forgot your password?

SELECT * Is Evil

By Edited Oct 15, 2015 0 0

Select * Is Evil
Although this is directed mostly at its' usage in PHP/MySQL, the reasoning is appropriate to any database system and the language used to retrieve data from it that operates in a similar manner.

* is a wildcard when used in this manner, and will retrieve all the data, unlike a specific query, which will only retrieve the requested data.

Database tables are composed of rows and columns. The rows are the entries in the database, the columns are details for each entry.

For example, a database built to handle user data on a site could have columns for username, real name, about, date joined, email address, password, website and so on.

Each user would have one row in this database.

A bad way of retrieving the data on users where you just want to get their username and email address is like this (shortened example):

SELECT * FROM table_users WHERE...

A good way of retrieving the data is like this(again shortened):

SELECT column_username, column_email FROM table_users WHERE...

SELECT * is really a lazy way of coding a database query. Instead of working out which specific columns you need data from, you just request the data from every column.

The more rows of data the query requests, the more columns each row has, the greater the amount of unneeded information retrieved.

The more data retrieved, the greater the execution time of the script retrieving it and the greater the likelihood of it timing out and failing.

As an example, there was a script that had been coded this way. Initially, this wasn't a problem; the number of rows and columns wasn't that high.

More columns were added to allow more functionality, and more rows were created. Eventually, the overview page ran into problems.

There were over 4,000 rows in the database table. The overview page was pulling data from every row. Each row had getting on for 200 columns of data in it, although only about a dozen of those were actually needed for the overview.

As a consequence of using SELECT * in the first place, rather than specifically requesting only the needed data, every time the overview page tried to load, it was attempting to retrieve over half a million unneeded pieces (fields) of data. Needless to say, it stopped working. A simple change reduced the requested data to less than 50,000 fields, and the page worked again.

Even using SELECT * in a query that only ever retrieves one row of data from the database can still cause problems. Although the uneeded fields in that query is, even in tables with lots of columns, unlikely to breach 200, if that query is being run by lots of different users, the amount of requested data that is neither needed nor wanted accumulates.

If you only retrieve the data you need, debugging a script is easier also. If an error occurs when the query is run and you are using SELECT *, you will have to check every column of the database, rather than just those you actually needed.

SELECT * is evil. It increases database strain unnecessarily, reducing server performance and, in the case of shared hosting, may result in your site being suspended for using too many resources. Even on a dedicated server, given a big enough database, it can create enough server load to slow down or cripple the site. Don't use it. Ever.
Advertisement
Advertisement

Comments

Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology