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

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

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-