Unoptimized query

[cobaku]

is below query normal it sometimes takes 27 seconds on some big forum sections which kills performance like crazy

from what i understand it only counts number of threads in certain forums
yes there are alot of threads on my forum but is there anything i can do to optimize this query, there is 1 more but i can live with the other.

Somehow i feel like noone can help me.

PHP Code:

SELECT COUNT(*) AS threads, SUM(IF(thread.lastpost > 1253852688 AND open <> 10, 1, 0)) AS newthread

FROM thread AS thread


WHERE forumid = 1220
    AND sticky = 0
    
     AND visible IN (0,1,2)

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    thread    range    forumid    forumid    6         119685    Using where

Time Before: 0.24812 seconds
Time After: 3.76783 seconds
Time Taken: 3.51971 seconds

Memory Before: 3,558.711 KB
Memory After: 3,558.566 KB
Memory Used: -0.145 KB 


[Dean C]

How many threads are we talking? Also where did you pull that query from?

[sockwater]

Originally Posted by Dean C

How many threads are we talking? Also where did you pull that query from?

forumdisplay.php line 720 in 3.8.4.

[Dean C]

I don't have a copy of vB's database handy to check, so check this index doesn't already exist. Try creating an index on the thread table with these fields in this order:

INDEX(forumid, sticky, visible)

Saying that, that query shouldn't be be particularly show. Can you also paste the full profiling output.

[kmike]

The query is already using that index, according to EXPLAIN in the first post:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    thread    range    forumid    forumid    6         119685    Using where

It's just that the number of matching rows is too big.
I'm afraid nothing could be done from the vBulletin side of things. Maybe some MySQL server tweaking will help to speed up that query.

[Dean C]

Originally Posted by kmike

The query is already using that index, according to EXPLAIN in the first post:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    thread    range    forumid    forumid    6         119685    Using where

It's just that the number of matching rows is too big.
I'm afraid nothing could be done from the vBulletin side of things. Maybe some MySQL server tweaking will help to speed up that query.

Yep I did say check the index doesn't already exist

KEY forumid (forumid, visible, sticky, lastpost),

[Adrian Schneider]

All code optimizations aside, you could edit this forum so it only shows threads from the past 6 months or year instead. This should significantly reduce the # of threads and still utilize the index.

[kmike]

Oh yes.
Hopefully the default sort order for that forum is by the last post time, otherwise the index wouldn't work.

[cobaku]

thanks so much for the replies , i think i understand the solution from the replies
i am on it right now

that forumid had 120.000 threads

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

i could only do siradrian's solution and it works.

Do you think this mod will help me with only single query
https://vborg.vbsupport.ru/showthread.php?t=210897
it looks a little hard.

[Adrian Schneider]

Afraid not. That mod is just to help automate common administrative queries. The easiest solution to implement is changing the option I mentioned above.

If you do not want to do that (and honestly I don't see why not...) then here is my thought.... Why on earth would you want to browse through 120,000 threads? The data does not get lost; you can still search and find it. I don't see people going past about 5-10 pages. Maybe 20 if they are bored. People use searching.

If your forum is that busy, where you cannot restrict posts by the past 6-12m because users will want to browse further, then I would suggest that you create some subforums and shift the content there instead. This way you can index by forumid and get way faster results without losing any user experience.

Anyway, if you insist on returning huge datasets back to the user, then here are more ideas:

Upgrade your hardware - This is generally expensive and will only solve the problem temporarily.

Implement caching - If you use 3rd party caching, I suppose it's of medium difficulty to set up. If you use MySQL's query cache, it would be pretty simple. In this example, all you'd need to do is some normalization on the linux timestamp it uses in the field list. If you round it to the nearest thousand or even hundred, it would be utilized. The way it's set up now, it can't be used. What this would do is have the query results re-used (instant repeat queries) until the data changes and invalidates it.

Again, especially if you use MySQL query caching, this is a band-aid solution.

Implement Sphinx -This is quite difficult / expensive to set up, but it's probably the most beneficial in terms of performance and growth support. Sphinx is a search daemon which is often used to replace keyword searching, but it can also perform full data scans VERY quickly. You can have it scan through hundreds of millions of rows in the time it would take MySQL to scan tens of thousands. For a board your size, it would be bringing the data back in under a tenth of a second easily.