开发者

Optimizing MySQL table structure. Advice needed

I have these table structures and while it works, using EXPLAIN on certain SQL queries gives 'Using temporary; Using filesort' on one of the table. This might hamper performance once the table is populated with thousands of data. Below are the table structure and explanations of the system.

CREATE TABLE IF NOT EXISTS `jobapp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(50) NOT NULL,
  `icno` varchar(14) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `icno` (`icno`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `jobapplied` (
  `appid` int(11) NOT NULL,
  `jid` int(11) NOT NULL,
  `jobstatus` tinyint(1) NOT NULL,
  `timestamp` int(10) NOT NULL,
  KEY `jid` (`jid`),
  KEY `appid` (`appid`)
) ENGINE=MyISAM;

Query I tried which gives aforementioned statement:

EXPLAIN SELECT japp.id, japp.fullname, japp.icno, japp.status, japped.jid, japped.jobstatus
FROM jobapp AS japp
INNER JOIN jobapplied AS japped ON japp.id = japped.appid
WHERE japped.jid = '85'
AND japped.jobstatus = '2'
AND japp.status = '2'
ORDER BY japp.`timestamp` DESC 

This system is for recruiting new staff. Once registrati开发者_高级运维on is opened, hundreds of applicant will register in a single time. They are allowed to select 5 different jobs. Later on at the end of registration session, the admin will go through each job one by one. I have used a single table (jobapplied) to store 2 items (applicant id, job id) to record who applied what. And this is the table which causes aforementioned statement. I realize this table is without PRIMARY key but I just can't figure out any other way later on for the admin to search specifically which job who have applied.

Any advice on how can I optimize the table?


Apart from the missing indexes and primary keys others have mentioned . . .

This might hamper performance once the table is populated with thousands of data.

You seem to be assuming that the query optimizer will use the same execution plan on a table with thousands of rows as it will on a table with just a few rows. Optimizers don't work like that.

The only reliable way to tell how a particular vendor's optimizer will execute a query on a table with thousands of rows--which is still a small table, and will probably easily fit in memory--is to

  • load a scratch version of the database with thousands of rows
  • "explain" the query you're interested in

FWIW, the last test I ran like this involved close to a billion rows--about 50 million in each of about 20 tables. The execution plan for that query--which included about 20 left outer joins--was a lot different than it was for the sample data (just a few thousand rows).


You are ordering by jobapp.timestamp, but there is no index for timestamp so the tablesort (and probably the temporary) will be necessary try adding and index for timestamp to jobapp something like KEY timid (timestamp,id)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜