** Using WeBWorK **

As promised in another thread, I have a trivial mysql script to add indexing for your webwork course databases. It should speed up some operations considerably. When looking up information in the database, it is the difference between doing a binary search on the key information vs. stepping through all rows of a given table.

It can be downloaded from

http://hobbes.la.asu.edu/webwork-stuff

The accompanying mysql command is listed there as well. It should only need to be run once per database.

John

<| Post or View Comments |>

I've altered my SQL code in Utils/CourseManagement.pm to add these indices as well. This requires some modifications to the $dbLayouts{sql} hash. There are some new fields added for each table, containing part of the SQL statements needed to add the appropriate indices. For instance:

        set_user => {
                record => "WeBWorK::DB::Record::UserSet",
                schema => "WeBWorK::DB::Schema::SQL",
                driver => "WeBWorK::DB::Driver::SQL",
                source => "dbi:mysql:webwork_$courseName",
                params => { %sqlParams },
                index1 => "ADD INDEX (user_id(10), set_id(15));",
                index2 => "ADD INDEX (set_id(15));",
        },

Note that I am leaving out the ALTER TABLE tableName part of the SQL statements, since I am adding the courseName prefix to the tableName in my code.

Zig

<| Post or View Comments |>

That looks interesting. The use of ALTER TABLE is because the database and tables would have already been created. It would certainly be more convenient to have it done automatically when the tables are created. In that case, it wouldn't be an alter table command, but it would be specifying keys for the tables. As I understand it, declaring keys on table creation is equivalent to adding an index later. I mention this mainly because I don't know for sure if this is true; so if someone knows better they will hopefully correct me.

A detail which is currently unresolved is how to code this into webwork itself so that webwork can do it when the tables are created. I hadn't thought of putting it in the dbLayouts{sql} hash.

John

<| Post or View Comments |>

A detail which is currently unresolved is how to code this into
webwork itself so that webwork can do it when the tables are created.

I've already done this. See

http://bugs.webwork.rochester.edu/show_bug.cgi?id=628

and in particular

http://bugs.webwork.rochester.edu/showattachment.cgi?attach_id=10

and verified that it works on my server.

In another vein, I have questions about the problem library stuff in your page. I downloaded the tarball, followed the instructions, and ran into several difficulties.

First of all the loadDB.pl script has several hash references like this: $dbLayouts{sql}->{problem_library_classify}. There is no such field in the standard conf/database.conf file.

Anyway, I made appropriate modifications in the loadDB.pl file, ran it successfully and have a loaded ProblemLibrary SQL database. But now what? I don't see any reference to this in the library browser page in my WWK2 server. (I did set webworkDirs{libraryRoot} in my conf/globa.conf file.) Are there some undocumented changes I am suppose to make in conf/database.conf related to $dbLayouts{sql}->{problem_library_classify}? Or am I supposed to be using some development version of WWK2?

Zig

<| Post or View Comments |>