More efficient way to fetch data from MySQL

Question

Suppose I have a MySQL table called "User".

ID, Name, Email, Age, Gender, Country, Hometown, City, Address, Relationship_Status, Employment_Status, IP, Registered_Time

Now, which query is more efficient to fetch all info of a user?

A)

SELECT * FROM User WHERE id=1

B)

SELECT ID FROM User WHERE id=1

SELECT Name FROM User WHERE id=1

SELECT Email FROM User WHERE id=1

SELECT Age FROM User WHERE id=1

SELECT Gender FROM User WHERE id=1

SELECT Country FROM User WHERE id=1

SELECT Hometown FROM User WHERE id=1

SELECT City FROM User WHERE id=1

SELECT Address FROM User WHERE id=1

SELECT Relationship_Status FROM User WHERE id=1

SELECT Employment_Status FROM User WHERE id=1

SELECT IP FROM User WHERE id=1

SELECT Registered_Time FROM User WHERE id=1

Thing is, depending on situation, I may or may not need all of the data. So, should I just fetch each data one by one when I need them or should I fetch all the data at once and then use as I need?

Thanks for helping!

Unless you have a huge number of columns, the slow part of any query is going and fetching the row. The number of columns that have to be copied back to the client is trivial. — Andy Lester
– Andy Lester, Commented Jan 26, 2015 at 22:57

O. Jones · Accepted Answer · 2015-01-26 22:18:14Z

4

A single query fetching multiple columns is almost always more efficient than multiple queries. Why? The query must be serialized by php, sent to the mySQL server, deserialized. Then the mySQL server has to do the WHERE operation to find the appropriate row or rows. Finally it has to serialize the results and send them back to php. The less you do all this, the less work the system has to do.

Pro tip: Don't use SELECT * . Instead enumerate the columns you want, like so.

SELECT ID, Name, Email, Employment_Status, Registered_Time FROM Users WHERE ID = 1

There are a lot of advantages to that approach, especially in queries that return many rows (which yours does not).

edited Jan 26, 2015 at 22:18

answered Jan 25, 2015 at 13:13

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tomas M Over a year ago

Ollie Jones's response doesn't answer the question. Ollie is talking about efficiency of single query fetching multiple rows, while the initial question is about fetching multiple columns. Then Ollie talks about enumerating of multiple columns instead of using *, which is also a different topic.

O. Jones Over a year ago

Oops, I meant to write "multiple columns," sorry. Fixed.

Tomas M · Accepted Answer · 2015-01-26 21:41:07Z

Each query to database costs something. PHP has to send the query to SQL server, the query analyzer has to parse your query, optimize it, translate it into some internal code, lookup the table, seek in the index file on the disk, seek in the data file on the disk.

The cost of selecting all columns at once using SELECT * is almost the same as cost of selecting a single column individually in one sql query.

When the data is read from the file on disk, it won't read just the 10 bytes you need, data from disk is always read in much bigger chunks by the operating system (like 4KB, it may depend).

Selecting one column like "SELECT Age ..." will read from disk the entire row anyway in vast majority of cases.

So to answer your question, SELECT * in your example will be more efficient since you'll go through the overhead of parsing a query just once.

Collectives™ on Stack Overflow

More efficient way to fetch data from MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related