You are correct. It's not necessary to change your SQL statements to take advantage of indices.
Redefining your tables may not be necessary, but I can see areas where you might see benefits. One example would be the stateVAL field in the first table. It looks like you're storing an abbreviation of each state's name. I'd just create a table for states and replace stateVAL with a StateID int(2). That way you're leveraging the relational database aspect of MySQL. You could probably go through the rest of the tables and look for this type of optimization, but how you model the data is really going to depend on the data itself, what you're trying to accomplish, and how much data you'll be working with. You may spend more time trying to optimize things than is necessary if you're dealing with a small data set... If you're not familiar with what I'm trying to describe, one good approach is to do a Google search using the words "good sql table design". It will bring up a lot of pages that talk about different approaches to optimizing the tables that you're creating. Here's some of the better links that I found when testing the search: http://dev.mysql.com/doc/mysql/en/Optimizing_Database_Structure.html http://www.onlamp.com/pub/a/onlamp/2001/03/06/aboutSQL.html http://www.sql-server-performance.com/database_design.asp I know that there are references to Microsoft's SQL server, but the SQL concepts apply to MySQL as well. -Ed > -----Original Message----- > here's a section of my tbl defs.. and the sql that accesses it.. are you > saying that i should simply redefine the tbls, to create an index on the > column name. as far as i can tell from reviewing mysql/google, i don't > have > to do anything differntly to the sql, in order to use the 'indexes'... is > this correct..??? > > > tbl defs:.... > create table universityTBL( > name varchar(50) not null default '', > repos_dir_name varchar(50) not null default '', > city varchar(20) default '', > stateVAL varchar(5) not null, > userID int(10) not null default'', > ID int(10) not null auto_increment, > primary key (ID), > unique key (name) > -- unique key (repos_dir_name) > )type =bdb; > > > create table university_urlTBL( > universityID int(10) not null default '', > urltype int(5) not null, > url varchar(50) not null default '', > -- userID int(10) not null default'', > actionID int(5) null default '', > status int(5) null default '', > ID int(10) not null auto_increment, > primary key (ID), > -- unique key (url, urltype), > unique key (url, universityID, urltype) > )type =bdb; > > > create table parsefileTBL( > university_urlID int(5) not null default '', > -- filelocation varchar(50) not null default '', > name varchar(50) not null default '', > -- urltype int(2) not null, > userID int(10) not null default '', > -- actionID int(5) null default '', > start_status int(1) null default '', > dev_status int(1) null default '', > test_status int(1) null default '', > review_status int(1) null default '', > prodtest_status int(1) null default '', > prod_status int(1) null default '', > op_status int(1) null default '', > fileversion varchar(50) not null default '', > fileID int(10) not null auto_increment, > primary key (fileID), > unique key (university_urlID, name) > )type =bdb; > > > > sql : > $query_ = "select > u1.urltype as type, > p1.start_status as status > from university_urlTBL as u1 > right join parsefileTBL as p1 > on u1.ID = p1.university_urlID > join latestParseStatusTBL as l1 > on p1.fileID = l1.itemID > where u1.universityID='$id'"; > > $query_ = "select > u4.username as user > from universityTBL as u1 > left join university_urlTBL as u2 > on u2.universityID = u1.ID > right join parsefileTBL as p1 > on p1.university_urlID = u2.ID > left join user_rolesTBL as u3 > on u3.itemID = u2.ID > left join users as u4 > on u3.userID = u4.user_id > where u2.urltype = u3.itemType > and u2.urltype = '$type' > and u3.process = '$process' > and u1.ID='$id' > group by date asc limit 1"; > > > i'm not sure i understand how the 'index' is supposed to speed up table > access/interaction... > > > as an example.. if i run the 1st query.. i get: > mysql> explain select > u1.urltype as type, > p1.start_status as status > from university_urlTBL as u1 > right join parsefileTBL as p1 > on u1.ID = p1.university_urlID > join latestParseStatusTBL as l1 > on p1.fileID = l1.itemID > where u1.universityID='40'; > +----+-------------+-------+--------+--------------------------+--------- > +-- > -------+------------------------------+-------+-------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > +----+-------------+-------+--------+--------------------------+--------- > +-- > -------+------------------------------+-------+-------------+ > | 1 | SIMPLE | l1 | index | itemID | itemID | > 12 | NULL | 11737 | Using index | > | 1 | SIMPLE | p1 | eq_ref | PRIMARY,university_urlID | PRIMARY | > 4 | colleges.l1.itemID | 1 | | > | 1 | SIMPLE | u1 | eq_ref | PRIMARY | PRIMARY | > 4 | colleges.p1.university_urlID | 1 | Using where | > +----+-------------+-------+--------+--------------------------+--------- > +-- > -------+------------------------------+-------+-------------+ > 3 rows in set (0.04 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
