vb.org Archive
Page 2 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   Forum and Server Management (https://vborg.vbsupport.ru/forumdisplay.php?f=232)
-   -   Unoptimized query (https://vborg.vbsupport.ru/showthread.php?t=223931)

cobaku 09-28-2009 08:37 PM
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 09-28-2009 11:40 PM
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 09-29-2009 04:39 AM
Quote:
Originally Posted by SirAdrian (Post 1892046)

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 09-29-2009 08:09 AM
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


All times are GMT. The time now is 05:55 PM.
Page 2 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01103 seconds
  • Memory Usage 1,736KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (4)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete