Project

General

Profile

Feature #5614

Change course_code to course_id in session_rel_course and session_rel_course_rel_user

Added by Yannick Warnier over 6 years ago. Updated over 2 years ago.

Status:
Feature implemented
Priority:
Normal
Category:
Database & API changes
Target version:
Start date:
06/10/2012
Due date:
% Done:

100%

Estimated time:
Spent time:
Complexity:
Normal
SCRUM pts - complexity:
?

Description

Some of the most used tables in chamilo in regards to sessions and their access are session_rel_course and session_rel_course_rel_user.

Using integers (smaller fields) will boost the execution of all related queries, as specified in the book "High Performance MySQL, 2nd Edition":

Smaller is usually better
In general, try to use the smallest data type that can correctly store and repre-
sent your data. Smaller data types are usually faster, because they use less space
on th disk, in memory, and in th CPU cache. They also generally require fewer
cpu cycles to process.
[...]
If you're in doubt as to which is
the best data type to use, choose the smallest one that you don't think you'll
exceed.

Simple is good
Fewer CPU cycles are typically required to process operations on simpler data
types. For example, integers are cheaper to compare than characters, because
character sets and collations (sorting rules) make character comparisons compli-
cated. Here are two examples: you should store dates and times in MySQL's
buil-in types instead of as strings, and you should use integers for IP addresses.
[...]

Avoid NULL if possible
You should define fields as NOT NULL whenever you can. A lot of tables include
nullable columns even when the application does not need to store NULL (the
absence of a value), merely because it's the default. You should be careful to
specify columns as NOT NULL unless you intend to store NULL in them.
It's harder for MySQL to optimize queries that refer to nullable columns,
because they make indexes, index statistics, and value comparisons more com-
plicated. A nullable column uses more storage space and requirs special pro-
cessing inside MySQL. When a nullable column is indexd, ir requires an extra
byte per entry and can ven cause a fixed-size index (such as an index on a sin-
gle integer column) to be converted to a variable-sized one in MyISAM.
[...]
The performance improvement from changing NULL columns to NOT NULL is usu-
ally small, so don't make finding and changing them on an existing schema a pri-
ority unless you know they are causing problems.
[...]

Associated revisions

Revision 3646ad04 (diff)
Added by Julio Montoya over 6 years ago

Adding course_id in the session_* tables see #5614

Revision eb6919fd (diff)
Added by Julio Montoya over 6 years ago

Replacing course code with course id from session_rel_course and session_rel_course_rel_user tables see #5614

History

#1

Updated by Julio Montoya over 6 years ago

I will also change the "smallint" to "MEDIUMINT" in this field: "session.id" seems too short now

#2

Updated by Julio Montoya over 6 years ago

  • Tracker changed from Bug to Feature

Changing to feature if you don't mind

#3

Updated by Julio Montoya over 6 years ago

course_id was added to the tables, first we need to "clean up" (move single queries in the sessionmanager class) the session_* calls

#4

Updated by Julio Montoya over 6 years ago

  • Status changed from Assigned to Needs more info
  • Assignee changed from Julio Montoya to Yannick Warnier
  • % Done changed from 0 to 10

Not sure if this is priority right now? because it will take some time to do all that stuff. Waiting for feedback.

#5

Updated by Yannick Warnier over 6 years ago

No, I don't mind.
No, it is not priority.
Actually, we should at least wait for the migration of ICPNA to be completed, otherwise we'll be in an akward situation :-)

#6

Updated by Yannick Warnier over 6 years ago

The current state of the queries is less than ideal. I've added a few comments in the commit, but at the very least we should point out here that:
  • the upgrade_... script should include a mechanism to update the course_code to course_id
  • the course_code field should be removed (it's already been removed in db_main.sql) in a _post script
  • the database version should always be changed, for any change, large or small, or incomplete, in the database scripts. This is what allows us to determine in which state a specific development installation is, and so to apply patches accordingly.
#7

Updated by Julio Montoya over 6 years ago

Yannick Warnier wrote:

The current state of the queries is less than ideal. I've added a few comments in the commit, but at the very least we should point out here that:
  • the upgrade_... script should include a mechanism to update the course_code to course_id

done

  • the course_code field should be removed (it's already been removed in db_main.sql) in a _post script

done

  • the database version should always be changed, for any change, large or small, or incomplete, in the database scripts. This is what allows us to determine in which state a specific development installation is, and so to apply patches accordingly.

done

code was sent

#8

Updated by Yannick Warnier over 2 years ago

  • Category changed from System to Database & API changes
  • Status changed from Needs more info to Feature implemented
  • Assignee changed from Yannick Warnier to Jurgen Gaeremyn
  • Target version changed from 2.0 to 1.11.4
  • % Done changed from 10 to 100

Already done in 1.11

Also available in: Atom PDF