CODEWITHSUNDEEP
phpmysqlperformance
donnikitos
donnikitos

Reputation: 986

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

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?

Upvotes: 2

Views: 703

Answers (2)

jchook
jchook

Reputation: 7260

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.

Upvotes: 0

tereško
tereško

Reputation: 58454

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:

  1. select all you need from Users table and apply to User entity
  2. 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.

Upvotes: 1

Related Questions