Homepage is slow not the rest of the site?

[marcus123]

Moved to godaddy and heaving all sorts of issues. The site loads fast except the home page takes 2 seconds to load.

Generated in 2.0611131 seconds (3.09% PHP / 96.91% MySQL)

SQL Queries: 14 / Global Parsing Time: 0.0394151 / Memory Usage: 7 MB
PHP version: 5.4.43 / Server Load: 1.84 / GZip Compression: Disabled

On other always has Server Load 0 now it's like 4 man that is crazy. PHP memory limit is 256MB can this be the issue?


Generation Statistics
Page Generation Time: 2.25245308876 seconds No. DB Queries: 13
PHP Processing Time: 0.1078687 seconds (4.79%) DB Processing Time: 2.1445844 seconds (95.21%)
Extensions Used: mysqli, xml Global.php Processing Time: 0.1104200 seconds
PHP Version: 5.4.43 Server Load: 2.23
GZip Encoding Status: Disabled No. Templates Used: 43 (43 Cached / 0 Manually Loaded)
Memory Usage: 7 MB (7340032 bytes) Memory Limit: 256M


Found the problem it's a query i have!

Can anybody tell me why it takes 2 sec to generate???

$query = $db->query("
        SELECT t.tid, t.fid, t.subject, t.dateline, 
       t.lastposter, t.lastposteruid, f.name,
       u.usergroup, u.displaygroup, p.displaystyle AS threadprefix, l.displaygroup AS ldisplaygroup, l.usergroup AS lusergroup, s.tid as tagstid, s.tags
        FROM ".TABLE_PREFIX."threads t
        LEFT JOIN ".TABLE_PREFIX."threadprefixes p ON (p.pid=t.prefix)
        INNER JOIN ".TABLE_PREFIX."forums f
       ON (f.fid = t.fid)
        LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
        LEFT JOIN ".TABLE_PREFIX."users l ON (l.uid=t.lastposteruid)
		LEFT JOIN ".TABLE_PREFIX."tags s ON (t.tid=s.tid)
        WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%'
        ORDER BY t.dateline DESC LIMIT $max"); 

[dragonexpert]

Start by removing the part that says AND t.closed NOT LIKE 'moved|%'. Using LIKE on a column without a text index makes it scan the whole table which is slow. You may not have noticed it before because you may have had a more powerful server with your other webhost.

In the while loop that follows that query add this code:

if(!$variablename['moved'])
{
// The rest of the code in the while loop goes here.
}
$variablename['moved'] = "";

[marcus123]

Buddy please excuse me but I am not sure how to make your code work?

if(!$variablename['moved'])
{
// The rest of the code in the while loop goes here.
}
$variablename['moved'] = ""; 

$query = $db->query("
        SELECT t.tid, t.fid, t.subject, t.dateline, 
       t.lastposter, t.lastposteruid, f.name,
       u.usergroup, u.displaygroup, p.displaystyle AS threadprefix, l.displaygroup AS ldisplaygroup, l.usergroup AS lusergroup, s.tid as tagstid, s.tags
        FROM ".TABLE_PREFIX."threads t
        LEFT JOIN ".TABLE_PREFIX."threadprefixes p ON (p.pid=t.prefix)
        INNER JOIN ".TABLE_PREFIX."forums f
       ON (f.fid = t.fid)
        LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
        LEFT JOIN ".TABLE_PREFIX."users l ON (l.uid=t.lastposteruid)
        LEFT JOIN ".TABLE_PREFIX."tags s ON (t.tid=s.tid)
        WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%'
        ORDER BY t.dateline DESC LIMIT $max"); 

while($row = $db->fetch_array($query))

        {
// the code
}

Thanks buddy a lot! I didn't had this issue on IPAGE. Server load was always 0 now it's like 2 to 3 One question please?

I have more PHP memory than on IPAGE 512MB but is there a way to increase the SQL memory or something to make it perform better?

I mean 90% of what value? And what values server load takes into account?

I mean what makes the database to be so slow on IPAGE it was very fast?

[Destroy666]

He probably meant something like:

while($row = $db->fetch_array($query))
{
    if(substr($row['closed'], 0, 5) == 'moved')
    {
        continue;
    }

    // the rest...
}

Of course you need to select t.closed too..

is there a way to increase the SQL memory or something to make it perform better?

You can't do nearly anything on a shared hosting.

[marcus123]

This query loads fast! But then I add:

LEFT JOIN ".TABLE_PREFIX."tags s ON (t.tid=s.tid)

it stalls!

Could you tell me what makes SQL faster is there some kind of memory like PHP memory limit?

Maybe it's better to juts use another query to fetch tags for each thread????

[dragonexpert]

What's your table structure for your tags table. Include any indices you have.

[marcus123]

The table only contains 3 columns:

TAGID - tag id
TID - thread id
TAGS - thread tags

So when you create a thread this table will store the tags and the TID.

So in the query above I wanna fetch latest threads and their tags! But for some reason TAGS table slows the SQL dramatically down.

Maybe the table needs to be optimized or repaired! How to check if the table is OK. I moved to another hosting maybe some went wrong!

I have this same plugin on my other site howto-makemoney.com and it doesn't have this issue maybe cause it doesn't have the same amount of threads???????

What do you recommend? Would you mind to post complete code of your suggestion?

I am guessing it might be also scanning the tags for NOT LIKE 'moved|%'

[Destroy666]

I am guessing it might be also scanning the tags for NOT LIKE 'moved|%'

That's why you should have removed that from the query and used the code I mentioned above..

EDIT: ehhhh...

[marcus123]

D666 baby do you mean some like this?

$query = $db->query("
        SELECT t.tid, t.fid, t.subject, t.dateline, 
       t.lastposter, t.lastposteruid, f.name,
       u.usergroup, u.displaygroup, p.displaystyle AS threadprefix, l.displaygroup AS ldisplaygroup, l.usergroup AS lusergroup, s.tid as tagstid, s.tags
        FROM ".TABLE_PREFIX."threads t
        LEFT JOIN ".TABLE_PREFIX."threadprefixes p ON (p.pid=t.prefix)
        INNER JOIN ".TABLE_PREFIX."forums f
       ON (f.fid = t.fid)
        LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
        LEFT JOIN ".TABLE_PREFIX."users l ON (l.uid=t.lastposteruid)
        LEFT JOIN ".TABLE_PREFIX."tags s ON (t.tid=s.tid)
        WHERE 1=1 $unviewwhere AND t.visible='1'
        ORDER BY t.dateline DESC LIMIT $max"); 


while($row = $db->fetch_array($query))
{
    if(substr($row['closed'], 0, 5) == 'moved')
    {
        continue;
    }

    // the rest...
} 

What did you mean by "Of course you need to select t.closed too.."


Maybe I can add another query that will generate only tags for one tid??

$test = $row['tid'];

$query2 = $db->query("
        SELECT t.tags,t.tid
        FROM ".TABLE_PREFIX."tags t
		WHERE t.tid = $test LIMIT 1
    ");