Quoth Ian Hardingham <[email protected]>, on 2011-02-01 15:10:43 +0000: [paragraphs reordered] > As there are hundreds of queries around my server codebase I would > prefer not to have to change every single one to use a numeric id for a > user rather than a string - but maybe this is the only real way of > optimising this properly?
That might be preferable, but what form do these queries take? Are you talking about joins with other tables, or lookups based on incoming parameters, or... ? If they're joins, then you need to change the semantics of the columns in the other tables and update the join expressions accordingly (which might propagate into the application if it also relies on those name columns being directly accessible). If they're lookups, then the effects could propagate into the surrounding application directly. Note that it's possible to do this sort of thing incrementally without totally breaking the queries in the middle, since you almost definitely want to keep a UNIQUE index on the name anyway. Regardless of that: > My core users table has a user defined by a string which is their name. > This string is used to address many other tables relating to users. Not > only is the primary key a string (which I understand is bad enough), but > I also have to use LIKE rather than = because the high level language I > use is a bit eccentric about case-ing. That sounds suspicious. If it's only case distinctions and not whitespace or other cruft, and in particular if the names in the database can be in a normalized form and it's only the parameters that are problematic, you can normalize the parameter on the SQL side first: « name = LOWER(:foo) », for instance. Or « name = TRIM(LOWER(:foo)) ». That might get you better index usage on the name regardless of whether you change to a synthetic integer primary key. Of course you have to do the normalization the same way when writing the records to the DB in the first place. > Thanks, > Ian ---> Drake Wilson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
