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

[Matthew M. <ma...@tu...>]

This is the first I have seen of it.

I am looking into your concerns and will write up my thoughts tomorrow.

Real quick:

>From what I have looked at the most common queries to phpws_key are:

where id = ?
where module = ?
where id = ? and restricted = ?

Understand rewriting key is a major undertaking so I must be positive
the delay would be worth it.

More tomorrow,
Matt


On Tue, 2006-05-02 at 14:46 -0500, Eloi George wrote:
> 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-
> >
> 
> 
> 
> -------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
-- 
Matthew McNaney
Electronic Student Services
Appalachian State University
http://phpwebsite.appstate.edu