MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ed Lazor Date:September 28 2004 7:37pm
Subject:RE: increasing mysql/table performance..
View as plain text  
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)

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