Unoptimized query

[cobaku]

i was already using your suggested solution, but i feel like i have to eliminate the problem completely.
thanks for your amazing detailed reply. I will definitely try sphinx.
i am using query caching and will also search what normalization on the linux timestampis

i am currently stuck at configuring
what i am trying to optimize is this

PHP Code:

    $threadscount = $db->query_first_slave("
        SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > $lastread AND open <> 10, 1, 0)) AS newthread
        $hook_query_fields
        FROM " . TABLE_PREFIX . "thread AS thread
        $tachyjoin
        $hook_query_joins
        WHERE forumid = $foruminfo[forumid]
            AND sticky = 0
            $prefix_filter
            $visiblethreads
            $globalignore
            $limitothers
            $datecut
            $hook_query_where
    "); 


what i have to configure is
at /etc/sphinx/sphinx.conf
below

PHP Code:

source src1
{
        type                                    = mysql

        sql_host                                = localhost
        sql_user                                = test
        sql_pass                                =
        sql_db                                  = test
        sql_port                                = 3306  # optional, default is 3306

        sql_query                               = \
                SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content \
                FROM documents

        sql_attr_uint                   = group_id
        sql_attr_timestamp              = date_added

        sql_query_info                  = SELECT * FROM documents WHERE id=$id
} 


i found an example at https://vborg.vbsupport.ru/attachmen...8&d=1210237129 i hope it works

--------------- Added [DATE]1254179115[/DATE] at [TIME]1254179115[/TIME] ---------------

ok it seems this config thing is much bigger than i can ever understand

was worth to try

but i may still implement search sphinx

[Adrian Schneider]

You'll have to make code modifications and play with the configuration quite a bit to get it to work. It's definitely not simple to set up. If I get some time in the future (probably not anytime soon, but maybe within a few months) I can post a tutorial for this.

However, I'm still of the opinion that browsing through over 10,000 rows of anything is not useful.

All normalization of the timestamps means is rounding... since the timestamp will increase every second, it can never re-use queries in the cache. If you round a few digits on the timestamps, the cache can be utilized.

[Dean C]

Originally Posted by SirAdrian

However, I'm still of the opinion that browsing through over 10,000 rows of anything is not useful.

Certainly not to find out newpost counts

[cobaku]

i never knew biggest boards uses sphinx indexing
other than search.

Thanks alot for the big tip.
I somehow managed to setup a sphinx search altough it does not list my older posts yet i have to fix it soon

I am quite happy with my new search system and may try to achive that indexing heavy queries with sphinx