PHP & MySQL performance - One big query vs. multiple small

Question

For an MySQL table I am using the InnoDB engine and the structure of my tables looks like this:

Table user

 id |  username  |  etc...
----|------------|--------
  1 | bruce      | ...
  2 | clark      | ...
  3 | tony       | ...

Table user-emails

 id |  person_id  |  email
----|-------------|---------
  1 |          1  | [email protected]
  2 |          1  | [email protected]
  3 |          2  | [email protected]

To fetch data from the database I've written a tiny framework. E.g. on __construct($id) it checks if there is a person with the given id, if yes it creates the corresponding model and saves only the field id to an array. During runtime, if I need another field from the model it fetches only the value from the database, saves it to the array and returns it. E.g. same with the field emails for that my code accesses the table user-emails and get all the emails for the corresponding user.

For small models this works alright, but now I am working on another project where I have to fetch a lot of data at once for a list and that takes some time. Also I know that many connections to MySQL and many queries are quite stressful for the server, so..

My question now is: Should I fetch all data at once (with left joins etc.) while constructing the model and save the fields as an array or should I use some other method?

... depends. Generally speaking using many simple queries will perform better than using one complicated one; but that depends on how complicated it is and how your indexes are set up. — CD001
– CD001, Commented May 24, 2017 at 15:54
You should just measure the performance and see if there is a problem. — pvg
– pvg, Commented May 24, 2017 at 15:56
@CD001 that comment actually surprises me, the amount that people try and cram as many SQL queries into one big query made me think fewer larger queries was generally speaking more efficient.....I guess I shouldn't follow the crowd..... :-D — Mm-Art-In
– Mm-Art-In, Commented May 24, 2017 at 15:58
@CD001 yep, same for me, quite surprising... well the main table has 25 fields, and then there are 11 of those multi-row tables. Performance wise - to load 60 of those rows it takes on my laptop (via xampp) around 10 seconds. — donnikitos
– donnikitos, Commented May 24, 2017 at 16:08
@Martin ... I've fallen into that trap before ;) I needed to run some big loops (on a dev box) and make liberal use of EXPLAIN before I realised that bigger isn't always better! — CD001
– CD001, Commented May 24, 2017 at 16:08

tereško · Accepted Answer · 2017-05-24 16:25:53Z

1

Why do people insist on referring to the entities and domain objects as "models".

Unless your entities are extremely large, I would populate the entire entity, when you need it. And, if "email list" is part of that entity, I would populate that too.

As I see it, the question is more related to "what to do with tables, that are related by foreign keys".

Lets say you have Users and Articles tables, where each article has a specific owner associate by user_id foreign key. In this case, when populating the Article entity, I would only retrieve the user_id value instead of pulling in all the information about the user.

But in your example with Users and UserEmails, the emails seem to be a part of the User entity, and something that you would often call via $user->getEmailList().

TL;DR

I would do this in two queries, when populating User entity:

select all you need from Users table and apply to User entity
select all user's emails from the UserEmails table and apply it to User entity.

P.S

You might want to look at data mapper pattern for "how" part.

edited May 24, 2017 at 16:25

answered May 24, 2017 at 16:20

tereško

58.5k26 gold badges100 silver badges151 bronze badges

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

6 Comments

donnikitos Over a year ago

the example with users + articles is something different, those are basically 2 different entities. In my case all those other tables belong to the same entity and for me right now all of them appear in a list.

tereško Over a year ago

Did you read the part that begun with "But in your example ..."?

donnikitos Over a year ago

yep, just now got to consider that solution. What about PHP? Wouldn't that kinda pollute the memory if I am not using some of the field of the main table?

pvg Over a year ago

@donnikitos you don't have to query across all fields. Additionally, again, 'is this using too much memory' and 'is this going to be fast enough' are best answered by simple measurements (which you can compare to initial estimates but the measurement is always better).

tereško Over a year ago

Something to consider: "premature optimization is root of all evil". You should always make the code easy to understand and only then look at whether you need to make it faster.

|

jchook · Accepted Answer · 2017-05-24 16:49:52Z

0

In my opinion you should fetch all your fields at once, and divide queries in a way that makes your code easier to read/manage.

When we're talking about one query or two, the difference is usually negligible unless the combined query (with JOINs or whatever) is overly complex. Usually an index or two is the solution to a very slow query.

If we're talking about one vs hundreds or thousands of queries, that's when the connection/transmission overhead becomes more significant, and reducing the number of queries can make an impact.

It seems that your framework suffers from premature optimization. You are hyper-concerned about fetching too many fields from a row, but why? Do you have thousands of columns or something?

The time consuming part of your query is almost always the lookup, not the transmission of data. You are causing the database to do the "hard" part over and over again as you pull one field at a time.

answered May 24, 2017 at 16:49

jchook

7,3085 gold badges42 silver badges44 bronze badges

4 Comments

donnikitos Over a year ago

I am just wondering how that would impact on the server. Of course I won't have thousands of columns, but somewhere around 70. And if I am loading for example 100 rows at once it would be already 7000 fields in total and this is what makes me think.

jchook Over a year ago

I would be far more concerned about 7000 queries to get 100 rows of data, vs 1 query to get 7000 fields. Even if every field was 20 bytes, we are still only talking about 140 KB of data.

jchook Over a year ago

Again, your saying it takes you 10 seconds to load 60 rows. That's insane. MySQL can query 60 rows, joined across 11 tables (each with millions of rows and 25 columns) in the blink of an eye.

Rick James Over a year ago

Large TEXT and BLOB columns are stored in a separate area. In this case, unnecessarily including them in the SELECT is a performance hit.

Collectives™ on Stack Overflow

PHP & MySQL performance - One big query vs. multiple small

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related