ok...
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)
-----Original Message-----
From: Ed Lazor [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 11:02 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: increasing mysql/table performance..
I usually create an index for each criteria being checked against in the SQL
statements.
For example, for this query
Select * from products where ProductID = 'aeg8557'
I'd create an index on ProductID. The same thing applies if you're pulling
data from multiple tables.
For this query:
Select products.ID, products.Title, categories.Title from products,
categories where products.ID = '5' AND products.CategoryID = categories.ID
I'd make sure that products.ID, products.CategoryID, and categories.ID all
have an index.
-Ed
> -----Original Message-----
> i've read/seen information regarding indexes within a table. i'm curious
> as
> to what i can do to speed up the response time/tbl interactions for the
> users....
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
