Support Forum

Advanced Search
Forum Scope


Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
general-topic
RSS creates long and slow query errors
Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 8:48 am

ran it on my staging server phpmyadmin, here was the results (15 total, Query took 1.6839 seconds.)

I accessed the feed through a feed reader and the errors showed in the error log, but not when I ran the sql query, feed reader took 2.003 seconds

Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 9:05 am

I tried cutting back on the amount it was showing in the feed, I dropped it to 10, then 5, and they were 1.9 and 1.7 seconds for the query, I then limited the feed to just topic names to see if that fixed it, and just the 10 topic names were above 2 seconds for the query

Avatar
Mr Papa
Simi Valley, CA
SP Master
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 8:34 pm

interesting that a feed reader increases the queries (not sure why)??

there is nothing special about the feed, especially if only 5 items?  way less complex a query than you forum front page...  and getting several times an order of magnitude less than that myself...

may need Andy to jump in here, but when you do it on your staging server, can you have it do an explain?  will give more info about what the query is trying to do and may offer insight...

also might be useful to see your keys for the sfgroup, sfforum and sftopic tables... make sure indexes look right... though if not, would expect the main forum view queries to be very poor then...

Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 8:44 pm

here is the explain

1 SIMPLE rufus_sfgroups ALL PRIMARY NULL NULL NULL 3 Using temporary; Using filesort
1 SIMPLE rufus_sfforums ref PRIMARY,groupf_idx groupf_idx 8 snapshot_kvr28.rufus_sfgroups.group_id 5 Using where
1 SIMPLE rufus_sfposts ref topicp_idx,forump_idx forump_idx 8 snapshot_kvr28.rufus_sfforums.forum_id 1138  
1 SIMPLE rufus_sftopics eq_ref PRIMARY PRIMARY 8 snapshot_kvr28.rufus_sfposts.topic_id 1  
1 SIMPLE rufus_sfmembers eq_ref PRIMARY PRIMARY 8 snapshot_kvr28.rufus_sfposts.user_id 1  
Avatar
Mr Papa
Simi Valley, CA
SP Master
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 8:55 pm

a temp table and filesort, eh?  seems bit overkill, but as I said, may need input from Andy...

are you suing MyISAM or InnoDB tables? 

oh,and can you paste in your my.ini setup?  be sure no passwords or other private info in what you post... ;)

Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 9:11 pm

it's a mix of both, sf_posts are InnoDB, where would I find the my.ini? have I posted anything private so far that you have noticed? I thought I had edited everything out

Avatar
Mr Papa
Simi Valley, CA
SP Master
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 9:32 pm

Mixture? How would the sfxxxx tables not all be the same?

my.ini will vary based on server os.. Usually /etc

There are some config options in the else for memory and things... Might be worth tweaking....

Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 9:41 pm

dunno, all are Myisam except for sflinks, sfoptions, sfpostattachments, and sfposts, those are InnoDB, we use wp-engine, we had the issue with the sql query error with the search function when we first started using SP until yellow fixed the issue so it worked with the way wp-engine has the tables set up

I will hunt down the my.ini

Avatar
Mr Papa
Simi Valley, CA
SP Master
Free Members
sp_UserOfflineSmall Offline
Oct 30, 2014 - 10:00 pm

Have to wonder if the mixed table types are contributing to this... Seems odd, but perhaps okay...

On your test site, might be worth downloading are plugin (on download page) for converting tables to InnoDB and see if it matters...

Avatar
kvr28
Member
Free Members
sp_UserOfflineSmall Offline
Oct 31, 2014 - 9:18 am

I'm sorry, which plugin?

Forum Timezone: Europe/Stockholm
Most Users Ever Online: 1170
Currently Online:
Guest(s) 1
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Mr Papa: 19448
Ike: 2086
Brandon: 864
kvr28: 804
jim: 643
FidoSysop: 577
Conrad_Farlow: 531
fiddlerman: 358
Stefano Prete: 325
Member Stats:
Guest Posters: 616
Members: 17343
Moderators: 0
Admins: 4
Forum Stats:
Groups: 7
Forums: 17
Topics: 10117
Posts: 79590