How to optimize this big table

Databases
1

hi there

I have to following table structure. The thing is that it is already big, and I’ll like to know what can be done in order to make it run faster.


CREATE TABLE IF NOT EXISTS `daily_panel` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `group_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `exam1` decimal(10,3) NOT NULL,
  `exam2` decimal(10,3) NOT NULL,
  `exam3` decimal(10,3) NOT NULL,
  `exam4` decimal(10,3) NOT NULL,
  `exam5` decimal(10,3) NOT NULL,
  `exam6` decimal(10,3) NOT NULL,
  `exam7` decimal(10,3) NOT NULL,
  `exam8` decimal(10,3) NOT NULL,
  `exam9` decimal(10,3) NOT NULL,
  `exam10` decimal(10,3) NOT NULL,
  `exam11` decimal(10,3) NOT NULL,
  `exam12` decimal(10,3) NOT NULL,
  `exam13` decimal(10,3) NOT NULL,
  `exam14` decimal(10,3) NOT NULL,
  `exam15` decimal(10,3) NOT NULL,
  `exam16` decimal(10,3) NOT NULL,
  `exam17` decimal(10,3) NOT NULL,
  `exam18` decimal(10,3) NOT NULL,
  `exam19` decimal(10,3) NOT NULL,
  `exam20` decimal(10,3) NOT NULL,
  `exam21` decimal(10,3) NOT NULL,
  `exam22` decimal(10,3) NOT NULL,
  `exam23` decimal(10,3) NOT NULL,
  `exam24` decimal(10,3) NOT NULL,
  `exam25` decimal(10,3) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `modified` (`modified`),
  KEY `created` (`created`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=137486 ;

I don’t know if it is useful, but this is the story behind this table: Each exam has a a maximum number of points. An exam can be passed of not. If the exam is passed, the user earns that exam points. There are the same number of exams in each day(maximum 25). The results of the group, is also important, because groups compete with each other. For each day, an HTML table with check boxes is generated for all the users of a group, and a teacher has to check the status of exams for each user.

2

How about normalizing the table?


  id` int(11) unsigned NOT NULL auto_increment,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `group_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `exam` int(11) NOT NULL,
  `result` decimal(10,3) NOT NULL,

Where exam contains the exam number, and result the result.

3

That is a way too big modification, and for the moment I don’t have the time and the courage to implement it. A lot of code has to be rewritten from the app layer in order to support this change.

4

Then can you explain what exactly you would like to make ‘run faster’ ? A query in particular?

5

ah… other peoples poor decisions – the story of our life. There might not be much that can be done given the poor decisions made in the beginning without a complete overhaul. I am assuming of course the op did not have any say in the creation of this schema… if so I guess it is their poor decisions…