Bad performance due to member URL's

Wizzo · #1 09-10-2008, 07:39 PM

We currently have member URL's set to members/[user_name].html however this makes VBSEo use some truly terrible SQL to locate users e.g.

Code:

select userid
from user
where username regexp "^(&[\\#\\da-z]*;|[^a-z\\d])*[nÑñ][aÀÁÂÃÄÅàáâãäå][nÑñ]d[oÒÓÔÕÖØòóôõöø](&[\\#\\da-z]*;|[^a-z\\d])+t[oÒÓÔÕÖØòóôõöø]rr[eÈÉÊËèéêë][sŠš](&[a-z]*;|[^a-z\\d])*$"
limit 1

Of course due to the use of regexp here, it forces a full table scan which is crippling for sites such as ours with 100,000's of users.

Surely a static map either added to the user table or created as a vbseo specific table should be whats used instead of this terrible SQL?

briansol · #2 09-10-2008, 08:11 PM

The only way to fix a full table scan is to include the userID in your re-write.

members/[user_id]-[user_name]/ for example

Wizzo · #3 09-11-2008, 12:54 PM

Thanks Brian changing all of the settings which just had [user_name] to [user_id]-[user_name] made a massive difference! The DB on a dual quad core machines was showing loads of 300% in top and its now down to ~10%.

This really should be the default as those queries are absolute killers especially as they are used for every post on a thread view.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Rewrite new vB tag format URL's to ZTT style URL's.	FleaBag	Custom Rewrite Rules	9	11-13-2008 03:42 AM
Performance hit	jdougher	General Discussion	2	02-21-2007 07:20 PM
vB SEO performance hit	bjornstrom	General Discussion	7	05-28-2006 05:35 PM
performance	ryanc	Pre-Sales Questions	2	01-22-2006 04:00 PM

SEO

Bad performance due to member URL's

Bad performance due to member URL's

Similar Threads