[nick2price]

We have a content management system running at my workplace, which is build using php. This management system is fairly large, and we have a problem with it at the moment as it runs extremely slow. I know this is the php forum, therefore comments may be bias, but is php a good choice for this type of system. We are investing a lot of money in a new system, and we have to decide what we want used to build it. I read that php does not have multi threading, which can be a bit of a downfall. So really just looking for your thoughts on whether php is good for this type of system, or what other recommendations you would make

cheers

Nick

[modi123_1]

If it's any consolation - the big three CMS sites all use a php backend: drupal, wordpress, and joomla.

[JackOfAllTrades]

Quote

it runs extremely slow

Usually, with something like a CMS (which is going to be reading/searching/updating a database), this is an indication of a poorly-tuned database.

[CTphpnwb]

PHP is multithreaded, but individual scripts are not. That's what allows the server to handle more than one user at a time. It would get bogged down quickly if you have a lot of users accessing the system and each script spawns multiple processes.

[Atli]

That's a bit misleading though, CTphpnwb. PHP is in no way multithreaded, but the servers running them (either the HTTP server itself or FastCGI) typically are and can run multiple PHP instances side by side in different threads. - The only way PHP can manage threading is to actually fork the process (and then, only on Unix systems), which is usually something to be avoided.

[CTphpnwb]

Ok, I should have said your scripts (or copies of the same one) run in multiple threads, one per user. The point is, processor time is already divided up amongst users, so dividing it further isn't going to be a cure all.

[nick2price]

Thanks for the replies. Well in total, there are about 20 users at any one time all on the system. I have been going through the code because I am going to liase with the new developers on what we want, but at the same time I am also going to attempt to make the current system more efficient, incase we need it as a backup.
Every sql query they make is like

$query = mysql_query("SELECT `id` FROM `enq` WHERE `status` = '1' AND `assignedto` IS NULL ORDER BY `date` DESC");  

I remember someone the other day saying prepared statements should be used instead. This management system is very database driven, so would changing all the quiries into prepared statements have an impact on speed?

Nick

[JackOfAllTrades]

Changing to prepared statements would indeed increase the speed a little, based strictly on the fact that the statement is compiled into the query cache, so parsing is not required. It also obviates the need for SQL injection preventive measures.

Here's the question: for this particular query, is there an index on the status and assignedto fields for the enq table? That would improve SELECT performance (while decreasing INSERT/UPDATE performance slightly). Those are the sort of things database tuning can assist with.

[e_i_pi]

nick2price, on 16 June 2012 - 12:12 PM, said:

Thanks for the replies. Well in total, there are about 20 users at any one time all on the system.

Without knowing what your application is doing, just that it is a CMS, 20 users should not be a problem. The fact that it is written in PHP shouldn't be a problem either - AFAIK Facebook, Wikipedia and Yahoo are all in PHP.

Quote

I remember someone the other day saying prepared statements should be used instead. This management system is very database driven, so would changing all the quiries into prepared statements have an impact on speed?

Yes, and the impact is hard to measure. For starters, there are 3 trips made to the DB when you first prepare a statement (I believe) - one to prepare the statement, one to check the parameters, one to retrieve the results. Depending on the sort of queries you're firing off, and how often you are firing off the same parameterised query, it could result in better performance or worse. Still, the upside is that you get security against SQL injection.

Also, there are a lot of instances under which a prepared statement will not get cached. If you are using MySQL < 5.1.17, there will be no caching. If the statement contains certain commands (such as CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), RAND()) it will not be cached. If it refers to functions or uses temporary tables it will not be cached. The full list can be found here.

JackOfAllTrades, on 16 June 2012 - 08:42 PM, said:

Here's the question: for this particular query, is there an index on the status and assignedto fields for the enq table? That would improve SELECT performance (while decreasing INSERT/UPDATE performance slightly). Those are the sort of things database tuning can assist with.

JackOfAllTrades raises a very important, if not crucial, point here. Assuming you are using the INNO_DB engine (and that you are using MySQL, rather than say PostgreSQL) have you indexed up the tables? Indexing can make a world of difference. At my work we use MSSQL, and we had one query that took 2 minutes to run. We applied one index, albeit to 6 columns, and the query execution time dropped to about 500ms.

My advice, if you are new to optimisation of SQL, would be to have a look at this slideshow. It goes through a lot of the common optimisation techniques, and talks about query structure, indexes, normalisation, architecture, and prepared statements. Excellent read in fact. As for specific advice, there are hundreds of optimisation techniques I could tell you about, but it's easier to go through them on a one by one basis. Using EXPLAIN in MySQL, or simply stopwatching query times can tell you where you have slow queries. Slow queries mean either bad queries, bad normalisation, bad indexes, or a genuinely hard-working query. It's usually one of the first three.

This post has been edited by e_i_pi: 16 June 2012 - 04:06 PM

[macosxnerd101]

Quote

Changing to prepared statements would indeed increase the speed a little, based strictly on the fact that the statement is compiled into the query cache, so parsing is not required. It also obviates the need for SQL injection preventive measures.

As e_i_pi touched upon, the real performance gain with prepared statements is that they work similarly to functions. Because they are cached on the SQL server, you can pass new parameters and the statement will fill them in. So the more you use the query, the more efficient it is over not using prepared statements.

[mccabec123]

Cheers for the slideshow e_i_pi, awesome share.

But yeh, to the OP, very rarely is a language to blame, it's usually the programmers terrible design that causes the biggest problem. The fact that they used 'mysql_query' shows their inexperience unless this was written years and years ago.

Another note to add is that PHP was designed for the web, unlike other languages such as Python and C etc. So everything is taken into account for the web. The only other alternative that is solely web based is ASP as far as I'm aware, but that's Microsoft's attempt at a web based language, because as always, another language isn't good enough for them.

Before someone tells me that PHP can be used for desktop applications, this was not it's original purpose, but I am aware of this.

This post has been edited by mccabec123: 16 June 2012 - 07:43 PM

[Atli]

mccabec123 said:

Another note to add is that PHP was designed for the web, unlike other languages such as Python and C etc...

That's not really something you should be taking into account when choosing a language, though. The fact that PHP is pretty much solely intended for server-side web development does in no way mean that it is somehow better suited for the task, or more efficient at it, than other languages with a broader range. Languages like ASP.NET, Java or even Python don't suffer one bit in the web development area because of their support for other types of programming.

[macosxnerd101]

Plus, I believe PHP has extensions to allow usage for desktop applications. See PHP-GTK.

[mccabec123]

macosxnerd101, on 16 June 2012 - 06:44 PM, said:

Plus, I believe PHP has extensions to allow usage for desktop applications. See PHP-GTK.

Did you even read what I said at the end?

[BenignDesign]

I imagine he did. And then proceeded to prove it incorrect. Simmer down, youngster.