Bad performance due to member URL's

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?

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

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.

I just realized this was why this query was showing up in my slow queries log. Any help with a 301 to redirect from old member url's without user-id to new one with user-id?

It is impossible to direct from no id To id without a lookup query, or a manual batch effort of creating xxxxxxxx redirects, which would just cause more harm than good.

FYI, I used this to redirect the old member pages to the homepage:

'^members/(.*)(with|friends)?-?(\w+)-?(page(\d))?\.html$' => '/content'

this accounts for the messaging and friends pages.