Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP/mysql - Queries vs arrays?

Tags:

php

mysql

theory

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?

like image 983
Jake Lowen Avatar asked Oct 23 '22 17:10

Jake Lowen


2 Answers

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.

like image 189
stivlo Avatar answered Oct 27 '22 09:10

stivlo


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.

like image 33
remiheens Avatar answered Oct 27 '22 11:10

remiheens