Re: [Phpwebsite-developers] Key documentation written (badly)

[Eloi G. <el...@re...>]

Hi Matt!
I never heard a reply on this message.  Just wanted to make sure you 
didn't miss it.
Cheers, Eloi



Eloi George wrote:
> Matthew McNaney wrote:
>> Seriously, I'm not sure. However, this table is indexed by the id and
>> modules using the key class keep this value in their own table. That
>> should be the only column searched on a regular basis. I am certainly
>> not an expert on optimization, but it would seem faster to have one
>> table of items than several tables with duplicate information.
>
> It's only faster for this query:
> SELECT * FROM keys WHERE key_id IN (8,434,353543,...,4434322)
> where the records involved have different module names (but you can't 
> search on that).  If they're all from the same module, all efficiency 
> is lost because of the unnecessarily large index.
>
> On a very large Key table phpWS will slow down when any of the 
> following happens:
> - you include anything other than an integer-indexed column in the 
> WHERE clause,
> - you include more than one column in the WHERE clause,
> - your db server uses a hard drive that's optimized for high 
> throughput instead of fast seeks.
>
> It's more efficient to break the table up into module-specific 
> subsets, eg. "mod_<module_name>_key" because the majority of queries 
> generated are for either 1) a specific key_id where the module is 
> known or 2) a search across key records (for active,
> create_date, update_date, etc.) where the module is known.
> Only a couple modules will search across different modulenames and 
> even then they only need results from a subset of the installed 
> modules on the site.  These searches can still be done just as 
> efficiently with UNION ALL clauses.  *More* efficiently if you count 
> the fact that indexes for unwanted modules are automatically excluded.
>
>>> Why are we not using the user ids of the author & editors?  I think 
>>> we should because I've had to change the usernames of 3 people over 
>>> the last 2 years, and it was a very long process.  I think it breaks 
>>> some sort of database normilization rule as well.
>>>   
>> Two reasons I did it this way. First, if I store the id, I have to join
>> with the users table to grab the display name. Not a big deal, but it is
>> certainly easier for developers to just have that information local.
>> Second, I was worried about deleted users. If a user has been removed,
>> their name will still be associated with the key. With an id, I would
>> get a FALSE when trying to pull the display name.
>>   
>
> But, users should never be deleted, just made inactive.  Let's say you 
> delete user "jack33" & a year later someone else registers with that 
> name.  That person automatically inherits all forum posts, articles, 
> calendar entries, etc. of the previous "jack33".  Chances are that 
> they'll start deleting/changing all that old content, because its not 
> theirs.  Indexing content on user_id will assure that that doesn't 
> happen.  Even if the siteadmin decides to reuse names, a JOINed query 
> would still be able to tell the difference between "jack33" and 
> "jack33 (dead user)"
>
>> I can change it, but then getting the display name would require an
>> extra database query per key listing. I am open to discussing options.
>> For example, perhaps just adding creator_id and updater_id columns?
>
> That sounds great!  "creator" and "updater" could remain as cached 
> data as long as the Coding Practices indicate that ownership & 
> permissions checks must always be made against "creator_id", as the 
> username held in "creator" may be incorrect, obsolete or a duplicate.
>
> creator_id should be indexed though, as many queries use this in the 
> WHERE clause.
>
> -Eloi George-
>