- Should foreign key columns each get their own index?
- How is JSON parsed into data structures in memory in a browser?
- Are multi-table joins inefficient?
- Can a web page make requests to a host other than the host from which the page itself was requested?
We'll come back to that. I am going to start by talking about a web browser (client) interacting via HTTP(S) with a web server. There are three pieces here, not two. The HTTP protocol matters since it is easy to work with and understand and there are lots of tools for working with it. There are some important differences between the web client/server environment and a more traditional client/server system:
- Connections are not persistent, and consist of only a single request and response. (Note that HTTP keep-alive does not change this; what persists is the TCP connection, and does not affect the application layer.)
- Interaction can only be initiated by the client, not the server. This is a result of the previous difference.
- The server cannot assume anything about the data received from the client.
Most people developing web sites/applications think in terms of the server software they are developing. Much as first-time GUI developers often find it baffling, the inversion of control involved in modern web programming confuses many developers. The server has full control when responding to a request but, once it has generated that response, control reverts to the client. For one thing, that means that data on the client does not get to the server unless the client decides to send it. It also means that data from the server does not get to the client unless the client decides to request it. One needs to work from the point of view of the user in front of the browser.
A common question on the Rails mailing list is how to use RJS to retrieve some value from the client. While the desire isn't ridiculous, and it can be done in a roundabout way with a certain amount of jumping through hoops, phrasing the question that way shows a lack of understanding of where the RJS-generated code will be executing. (The hoop jumping involves having the RJS generate an AJAX request back to the server to submit the value back to some URL on the server.)
There was a recent thread on the Rails list complaining about the functionality in Rails (largely RJS and various helpers) that attempts to hide the complexity of interactions between client-side and server-side code and largely results in maintainability problems in the code and misunderstandings for the developer. I don't agree with everything in either the original post or the various responses, but it highlights a problem Assaf identified months ago.
Let's go back to that quiz. You shouldn't have had to think too hard about any of these, and you should feel certain about your answers. And those answers should be:
- Should foreign key columns each get their own index? Sometimes. It depends very much on what queries will involve them. A join table (i.e. one with more than one foreign key that represents a many-to-many relationship between tables) usually benefits from an index on all foreign keys, sometimes even multiple indices of the same columns in different orders. Tables only queried by columns other than their foreign keys generally don't benefit from indexing those foreign keys, even if the table is usually joined against the tables to which those foreign keys refer.
- Are multi-table joins inefficient? This depends on the number of tables, available indices, and the database engine. Joining 18 tables in MySQL can make the query optimizer hang for hours (that's the query optimizer, not executing the query), regardless of available indices on the tables involved. A query on tables lacking indices on appropriate columns will require full table scans in any database, which is always slow (unless the unindexed tables have very few rows). It is always worth asking your database engine to explain and profile the queries you'll be running. Incidentally, database logs from running your unit/functional/integration/whatever tests are a great place to start.
How did you do? If you didn't get them all, you need to keep learning. If you got them all right, don't get too cocky; you may still not know everything you need to know to avoid the pitfalls of a code-generating framework. I keep learning about things I thought I knew thoroughly, and I wouldn't have it any other way. Enjoy!