Re: [WWdevel] relative speed of sql_single and gdbm

[John J. <jj...@as...>]

Hi,

In a previous e-mail I had found that the slowdown was due to 
case-sensitivity in the mysql index.  The index is not case sensitive, 
so if you force key elements to treated as case sensitive, then the 
index is ignored and the whole thing slows to a crawl with big databases.

Attached is a version of lib/WeBWorK/DB/Schema/SQL.pm which tries to 
adjust for this.  If you are making a deletion of a database record, or 
putting something into the database, then those calls have to use case 
sensitive key elements, so they will remain slow.  For select 
statements, the call can be not case sensitive, and then we filter out 
the records which we really want.

I have tested this, but it would be better if other people tested it 
too.  Unlike changes to other files, an error here can mess up an 
existing course.

I thought about other ways to handle this.  One I mentioned before was 
to try to change the data type of the key fields so that the indexing 
might end up case sensitive.  I haven't found a way to do that yet.

If the fact that inserting and deleting information from the database is 
still slow is a problem, then another possibility would be to add a 
unique numeric key to each table.  Then when you want to delete rows 
from a table, you select (case insensitively), pick out the ones which 
match your favorite key fields (including case), then use the unique id 
number for those rows in the actual deletion statement.  A similar 
strategy might work for put type statements.

John