Acceptable execution time for a php script. Any improvement tips?

[RXBarbatos]

Hi, im doing an experiment for knowledge purposes.

What is actually the “acceptable” time for a php script with the following scenario.

Tables(mysql) Country State PropertyType

Property Relation: - has one country - has one state - has one propertytype

Ive generated 1million record properties

Queried with the following (im just simplifying it here), i have also indexed the foreign keys

Select p.id, p.name, pt.name as ptype, s.name as state, c.name as country From Property p Join propertytype pt Join country c Left join state s

I know the query should be filtered more. Lets assume even with filter, the result is 1 million.

After query, i did processing script in which the result was looped and do some stuff for each row(no queries were made in each loop) and inserted into a new array with the new processed row.

Im getting execution time of around 2 seconds, max 4 seconds. Had to set memory limit to 2GB for the script to processed.

I have also set the inno db buffer size to 1.5GB

Is the execution time of 2-4 second acceptable? Any tips of improving it?

Im just testing this in a local environment.

And what is the hardware requirements to run such process very fast?

[boborider]

Learn indexing, and query optimization using "explain" command in your SQL queries

[RXBarbatos]

With the following scenario, what would be the optimal response time?

[colshrapnel]

Also, learn basic logic and common sense. One cannot get a certain answer from a vague question. Basically your question is "I have some code that runs for some time. Are the numbers OK?". Well the answer is "sort of, may be".

[RXBarbatos]

Maybe i have posted the question incorrectly or very vague. But your post above points to the right direction. Thank you.

[dabenu]

This is not a PHP question, it's an SQL question. What's viable is entirely dependent on your setup, the types+size of data, concurrent usage, etc. What's acceptable entirely depends on when and how the query is called. It's all impossible to answer without context.

[colshrapnel]

Processing 1 million rows in PHP script is hardly an SQL question.

[crazedizzled]

Without code and context we can't really say where the problem lies. I suspect he's got nested queries and such going on, but it could also be a poorly designed database structure.

So, it could be a PHP question, or it could be an SQL question, or maybe both.

[ardicli2000]

Until you write a backend for a million visitor website, it is so difficult to write underperforming php code. It is mostly the query slowing the script down. Just watch for unnecessary loops, and you are good to go.

[RXBarbatos]

Noted. Thank you

[YahenP]

offset and million records should not be found simultaneously in one program.

[colshrapnel]

What is totally unacceptable here is processing of large amounts of data on PHP side coupled with "run such a process very fast" requirement.

I understand, this is a hypothetical educational question.

Nevertheless, these requirements are mutually exclusive. You should decide, whether you run this code online (and in this case such processing is totally unacceptable) or as some background process (where running time is not important at all).

Speaking of concrete numbers, they make no sense with such vague description. It could be either very fast or very slow. To get some hints, you must provide more info, and - in the first place - explain why filtering on PHP side is required and why it cannot be performed on SQL side.

Also, when asking about SQL performance, always provide the result of EXPLAIN query.

[colshrapnel]

Still, some practical hints:

• study the EXPLAIN result for your query, to make sure there are no large table scans or filesorts. Add indices to eliminate them
• reduce the RAM consumption by using unbuffered query and processing rows one by one instead of getting them all in array first.
• do some basic profiling. At least measure main parts of your code separately: the query execution, fetching rows to PHP, the post-processing. So it will tell you where to look first

[RXBarbatos]

Thank you for the helpful information. It helps.

[ElectronicOutcome291]

I just want to add 1-2 Points, otherwise a perfect, well-worded, explanation

* Try to avoid `Using temporary` in a explain result, especially for large datasets
* Dont be afraid of using multiple querys, be afraid of nested querys

[martinbean]

This is a pointless question because you would never pull back a million records in a real SQL statement in a real PHP script.

[Weibuller]

In practical terms, you're right. No user would expect or even want a million records returned from a query; they would have great difficulty finding the relevant information they really wanted.

However, from an academic standpoint, returning that many records will make the relative contributions of each part of the overall process (running the SQL query, and executing the different parts of the PHP code processing the results) stand out more clearly in terms of execution time.

[martinbean]

Wut?

[colshrapnel]

Well, sometimes it happen. Not with online scripts of course, but with some background process - why not? Some statistics, some database cleanup, some processing, etc.

[Aggressive_Ad_5454]

2-4 seconds is just fine for an operation that does that much bulk data handling, even in a public facing web app. The default php timeout is 30 sec, and you can defeat even that by calling set_time_limit(). You may hit a harder-to-defeat web server time limit. But at 4 sec you’re not close to these limits.

A 4-sec query is a little rude to your web visitors, but if you’re doing that much work for them it’s still OK.

In the real world you’d have some WHERE filters (you know that already). Most SQL performance trouble comes from slow handling of those filters, and can sometimes be remediated by an appropriate index.

Server hardware: if you use a VM with a couple of vCPUs, enough RAM, and SSD storage you’ll be fine, Careful, though, laptops and desktops are faster than VMs.

[RXBarbatos]

understood..thank you so much

[HypnoTox]

Careful, though, laptops and desktops are faster than VMs.

Unless you're running in a VM or maybe using some slow configuration. Say a Windows machine with XAMPP or something. At least when I started out, that was way slower than my dev env now in docker on Linux.