[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    newbie: Overhead on MySQL MyISAM table (via phpMyAdmin 2.5.3)
From:       "James Werrbach" <jwerrbach () personnel ! com>
Date:       2004-03-28 19:58:14
Message-ID: 000a01c414ff$0935e700$0400a8c0 () rio
[Download RAW message or body]


I noticed an 'Overhead' notice in red text on one of my tables (see
structure below) when browsing via phpMyAdmin. Did an OPTIMIZE and it's fine
for now.
 
Of all the tables I've have, I didn't expect to see this notice as there are
only 8 records so far!
I have two larger message tables which have thousands of records, undergo
more traffic, and contain two 'text' field types each...no issues with them.
 
As this table will probably max out around a 1000+ records in its lifetime,
I'd expect not having to OPTIMIZE but maybe a few times(?) Something to
worry about?
 
Any suggestions?
 
Thanks people,
 
James
 
 
 
 
#
# Table structure for table `tbl_jobs`
#
 
CREATE TABLE `tbl_jobs` (
  `job_id` int(10) NOT NULL auto_increment,
  `emp_id` int(10) default NULL,
  `job_category` varchar(125) default NULL,
  `job_title` varchar(150) default NULL,
  `job_type` varchar(12) default NULL,
  `job_salary` varchar(25) NOT NULL default '',
  `job_company_name` varchar(125) default NULL,
  `job_city` varchar(50) default NULL,
  `job_location` varchar(6) default NULL,
  `job_number` varchar(12) default NULL,
  `job_is_deleted` varchar(25) default NULL,
  `job_view_count` int(6) default NULL,
  `job_date_created` datetime default NULL,
  `job_description` text NOT NULL,
  PRIMARY KEY  (`job_id`),
  KEY `job_location` (`job_location`(3))
) TYPE=MyISAM AUTO_INCREMENT=18 ;
 
#
# Dumping data for table `tbl_jobs`
#
 
More:
- Primary key is set (duplicates allowed) on job_id. Not really an issue as
there will *sometimes* be 2-10 identical jobs openings at times.
- Index is created on job_location with cardinality of 3, which is the only
user-defined search criterion placed into the SELECT.
- Records are never deleted here (job_is_deleted = 0 or 1 takes care of this
inside each SELECT).
- The only SQL actions performed are
    1.) Natural SELECT on all fields ("SELECT job_id, emp_id, ..........
WHERE job_is_deleted = 0 AND job_location = 'AL, US' ")
    2.) An UPDATE statement on job_view_count everytime a complete recordset
is queried via the job_id ("...SET job_view_count = job_view_count +
1...WHERE job_id = x")
    3.) An UPDATE statement when a job is deleted ("...SET job_is_deleted =
1 WHERE job_id = x")

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.644 / Virus Database: 412 - Release Date: 3/26/2004



[prev in list] [next in list] [prev in thread] [next in thread]