Firefox PHP

speed problem

Posted by manuvb 
All files from this thread

File Name File Size   Posted by Date  
list_new.php 26.8 KB open | download manuvb 01/05/2007 Read message
speed problem
January 05, 2007 02:57PM
Hello,
i have wrote a hack to display the messages of all forums running in my phorum program in a same page. It's an adaptation of list.php. You can see it functioning here: [les-mathematiques.u-strasbg.fr]. But i have a speed problem. The list.php is very quick to run but my list_new.php is very slow. Could it be a cache problem? I send my list_new.php with this message.
Thank's in advance for your answer (and sorry about my english...)
Emmanuel Vieillard Baron



Edited 1 time(s). Last edit at 01/05/2007 02:57PM by manuvb.
Attachments:
open | download - list_new.php (26.8 KB)
Re: speed problem
January 05, 2007 03:19PM
I don't think there would be any cache problem. Caching of data is used for speeding things up on busy servers, but without caching data Phorum pages should run quick too. And if there were a cache problem, it would probably affect your list.php script as well.

I see that you added extra SQL functions to your list_new.php. That makes the database queries that are executed the first candidate for inspection. Try timing the SQL functions and see if one of them is extremely slow. If this is the case, then take the SQL query/queries from that function and see what they do if you run them directly in the database. It might be that you have created some query for which a database index is missing, which causes major slowdowns on the select statements. If you have a slow query, then execute the query with "EXPLAIN " in front of it. That will MySQL explain to you in what way the query is handled.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: speed problem
January 05, 2007 04:46PM
Yes, you are right. Thank's a lot for your response. My sql function take more than 40s to be executate...

SELECT phorum_messages.author, phorum_messages.datestamp, phorum_messages.email, phorum_messages.message_id, phorum_messages.meta, phorum_messages.moderator_post, phorum_messages.modifystamp, phorum_messages.parent_id, phorum_messages.msgid, phorum_messages.sort, phorum_messages.status, phorum_messages.subject, phorum_messages.thread, phorum_messages.thread_count, phorum_messages.user_id, phorum_messages.viewcount, phorum_messages.closed, phorum_messages.forum_id
FROM phorum_messages
USE INDEX ( list_page_float )
WHERE modifystamp >0
AND STATUS =2
AND parent_id =0
AND sort >1
ORDER BY modifystamp DESC
LIMIT 0 , 50

I have construct it with this who come from the original function phorum_db_get_thread_list:

SELECT phorum_messages.author, phorum_messages.datestamp, phorum_messages.email, phorum_messages.message_id, phorum_messages.meta, phorum_messages.moderator_post, phorum_messages.modifystamp, phorum_messages.parent_id, phorum_messages.msgid, phorum_messages.sort, phorum_messages.status, phorum_messages.subject, phorum_messages.thread, phorum_messages.thread_count, phorum_messages.user_id, phorum_messages.viewcount, phorum_messages.closed
FROM phorum_messages
USE INDEX ( list_page_float )
WHERE modifystamp >0
AND forum_id =2
AND STATUS =2
AND parent_id =0
AND sort >1
ORDER BY modifystamp DESC
LIMIT 0 , 48

the unique difference is that i have removed "AND forum_id =2".

When i replace this line by "AND forum_id in (2,3,4,5,6,7,8,9,10,2,11,12,13,14)" to scan all my forums it takes just 10s to be executate but it still too long. Have you an idea to optimizate this queries?

Emmanuel Vieillard baron
Re: speed problem
January 05, 2007 06:26PM
You tell the SQL query to use the "list_page_float" index. That one contains the forum_id as the first component. But you do not want to search by forum_id. Therefore, this index is useless and MySQL will revert to a simple table search (like finding a telephone number by going through the whole book instead of using an index). Using an IN (...) query won't really help here. You're not interested in the forum_id for selecting messages, so the best thing would be to fully keep it away from your statement.

You could try to do the following:

1) Create a special index for this purpose (takes a little while on a large database):
create index list_page_float2 on phorum_messages(parent_id,modifystamp);

2) Modify the query to use this new index "list_page_float2" instead of 'list_page_float".

A quick check on my system show a query time that decreased from 0.50 seconds to 0.00 seconds (selection over about 181,000 messages in the database). My bet is that your page will perform better too.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: speed problem
January 05, 2007 10:11PM
Thank's a for this advice. The problem is now solved. I would never have found it alone. I will clean my program and post it here. Perhaps could it be useful for somoene.
Emmanuel Vieillard Baron.
Sorry, only registered users may post in this forum.

Click here to login