CODEWITHSUNDEEP
phpmysqltheory
Jake Lowen
Jake Lowen

Reputation: 929

PHP/mysql - Queries vs arrays?

I am writing a fairly simple webapp that pulls data from 3 tables in a mysql database. Because I don't need a ton of advanced filtering it seems theoretically faster to construct and then work within large multi-dimensional arrays instead of doing a mysql query whenever possible.

In theory I could just have one query from each table and build large arrays with the results, essentially never needing to query that table again. Is this a good practice, or is it better to just query for the data when it's needed? Or is there some kind of balance, and if so, what is it?

Upvotes: 5

Views: 4645

Answers (4)

bjelli
bjelli

Reputation: 10070

I absolutely concur with chris on optimizations: the LAMP stack is a good solution for 99% of web apps, without any need for optimization. ONLY optimize if you really run into a performance problem.

One more thought for your mental model of php + databases: you did not take into account that reading a lot of data from the database into php also takes time.

Upvotes: 0

remiheens
remiheens

Reputation: 367

it depends ...

Try each solution with microtime function and you'll seethe results.

I think a MySQL Query cache can be a good solution. and if you've filtering on , you can create view.

Upvotes: 1

stivlo
stivlo

Reputation: 85476

PHP arrays can be very fast, but it depends on how big are those tables, when the numbers get huge MySQL is going to be faster because, with the right indexes, it won't have to scan all the data, but just pick the ones you need.

I don't recommend you to try what you're suggesting, MySQL has a query cache, so repeated queries won't even hit the disk, so in a way, the optimization you're thinking about is already done.

Finally, as Chris said, never think about optimizations when they are not needed.

About good practices, a good practice is writing the simplest (and easy to read) code that does the job.

If in the end you'll decide to apply an optimization, profile the performance, you might be surprised, by unexpected results.

Upvotes: 3

Shomz
Shomz

Reputation: 37701

If you can pull it off with a single query - go for it! In your case, I'd say that is a good practice. You might also consider having your data in a CSV or similar file, which would give you even better performance.

Upvotes: 0

Related Questions