List:General Discussion« Previous MessageNext Message »
From:bruce Date:September 28 2004 7:06pm
Subject:RE: increasing mysql/table performance..
View as plain text  
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:Ed.Lazor@stripped]
Sent: Tuesday, September 28, 2004 11:02 AM
To: bedouglas@stripped; mysql@stripped
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....

Thread
increasing mysql/table performance..bruce28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
  • Re: increasing mysql/table performance..Jeff Mathis28 Sep
Re: increasing mysql/table performance..Jeff Smelser28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
  • Re: increasing mysql/table performance..SGreen28 Sep
RE: increasing mysql/table performance..bruce28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
RE: increasing mysql/table performance..SGreen28 Sep