-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter, et al --

...and then Peter Brawley said...
% 
% Your reference to "having the table sorted" suggests physical table sorting,

Right...


% but a basic characteristic of a relational DBMS is that data retrieval does
% not depend on physical row order. The actual order of rows in a MySql table

OK.  That's what I figured, especially since I didn't see anything about
how to insert a record sorted or unsorted or such, but couldn't find
anything authoritative.


% is entirely arbitrary, and shouldn't be your concern except perhaps for huge
% OLAP tables.

Right.  That's why, when they're needed, database architects are *really*
needed :-)


% 
% So I think the question you are asking comes down to this: when does an
% index provide a performance advantage? One answer is: when the rows you are

That's the second question, figuring that indexed fields are what is
going to make things faster :-)


% retrieving form a very small subset of the table you are querying, the
% effect of the index then vbeing to greatly diminish the number of rows that
% have to be physically read to return the desired result set.

That sounds like this case...  A hundred or a thousand clients, and I
want the card or cards for just one of them.  And so...  How does this
look?

Assuming a table 'clients' with a client ID and a table 'cards' with a
card ID (since multiple related clients might use the same card, or more
particularly since I will probably separate the actual card numbers
behind a security scheme), is

  create table ccards
  (
    # ID number
    id int not null default 0 auto_increment primary key ,
    client int ,        # references client.id
    index (client) ,    # fast indexed lookups
    type varchar(10) ,  # MC, Visa, AmEx, Disc, ...  maybe a set()?
    card int            # references cards.id
  ) ;

a valid definition?  And, meanwhile, how about

  create table clients
  (
    # ID number
    id int not null default 0 auto_increment primary key ,
    # last, first names
    lname varchar(40) , 
    index (lname) ,
    fname varchar(40) , 
    index (fname) ,
    mname varchar(40) ,
    ...
  ) ;

for a client table with indexed first and last name fields?


% 
% PB

I'll ask about char vs varchar and text next time :-)


Thanks & HAND

mysql query,
:-D
- -- 
David T-G                      * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/    Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE9/NpGGb7uCXufRwARAt2AAJ9THBEarHIK5/42BMlNZw7JPAqIvACeJi2X
hVVy3K3LPq0dlCNHipKw/VY=
=rdKs
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to