@geoffham: I totally understand where you're coming from on not wanting to do a rewrite (why break working code), but I have to agree with harry - I'd seriously recommend re-writing the schema sooner rather than later - RDBMSs in general aren't designed with frequent schema updates in mind, and even ignoring the current speed problems you may well be making more work for yourself tracking down obscure problems down the road.
As you see from the schema that harry posted above, the typical method is to design a set of tables each of which has a unique id column. When you need to build links of arbitrary degree between these tables, you use a separate mapping table, each row of which represents a single link. This is often referred to as a normalised schema, as it minimises duplication of information between tables.
So you'll often see a table specification like this:
CREATE TABLE customers (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(128) NOT NULL,
lastName VARCHAR(128) NOT NULL,
...
PRIMARY KEY (id)
) ENGINE=InnoDB;
The AUTO_INCREMENT on the id column means that you should never need to specify a value for it - each time you insert a row, a unique numeric ID is assigned to the id column for you. I don't want to get bogged down in details, but the PRIMARY KEY constraint makes it very efficient to look up rows via their id (which makes table joins faster) and also imposes a UNIQUE constraint (so each row has a unique value in the id column).
The schema for the orders table might be:
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer BIGINT UNSIGNED NOT NULL,
orderTime DATETIME NOT NULL,
...
PRIMARY KEY (id),
CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES customers(id)
) ENGINE=InnoDB;
Note the foreign key constraint - this ensures that MySQL won't allow rows to be created which don't reference a valid customer. This is only possible because we're using the InnoDB engine, the old MyISAM engine doesn't support them. Foreign key constraints are generally a good idea to keep things consistent, but you do have to be careful (for example, MySQL won't let you remove a customer without first removing their orders - this is a Good Thing(tm), but something to bear in mind).
Similar, the lineItems table might be:
CREATE TABLE lineItems (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order BIGINT UNSIGNED NOT NULL,
product BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
...
PRIMARY KEY (id),
CONSTRAINT fk_order FOREIGN KEY (order) REFERENCES orders(id),
CONSTRAINT fk_product FOREIGN KEY (product) REFERENCES products(id),
CONSTRAINT unq_order_product UNIQUE (order, product)
) ENGINE=InnoDB;
Here we see some more foreign key constraints and also a UNIQUE constraint which ensures that each pair of order and product must be unique - so each order can only have one line item per product type. This is OK because the quantity column allows multiple instances, but might cause problems if you wanted to make the items different prices due to, for example, a buy-two-get-one-free offer. Personally I'd represent such offers in a separate table in the database (and as separate items on the invoice with effectively a negative price), but that's up to you. Anyway, I digress...
The JOIN that harry posted above is fine, but personally I'd be explicit about it being an INNER JOIN - it's easier to figure out what MySQL is doing the more specific you are. I'd also use the table names (or aliases for them) in the result columns, just in case two tables have the same column name. Further, I'd suggest using the ON clause for the JOIN instead of a WHERE because it's generally less ambiguous (see this SO answer for a more complete discussion than I could manage).
SELECT P.name, P.price, L.quantity
FROM lineItems AS L INNER JOIN products AS P ON L.order=P.id
WHERE L.order=?;
Hope that discussion was some help - feel free to ask about anything I mentioned if you're not sure about it.
Disclaimer: I'm not, and never have been, a DBA - my knowledge of MySQL is just what I've picked up over a few years of using it. Also, that was all from memory so forgive any syntax errors.
EDIT: Given that the context of this post was the speed of MySQL I should probably point out that the use of InnoDB, FOREIGN KEY and UNIQUE constraints will actually slow things down slightly - their purpose is to ensure consistency within the DB, not increase speed. The reworking of the SELECT statement and the addition of PRIMARY KEY directives, however, should provide some boost to performance. Depending on the queries to be executed it would probably be useful to add some additional secondary indicies to the tables, but this has to be done once the primary use cases are known. It's worth thinking about early, however, as once tables become large then adding an index can become an extremely slow operation which locks tables from use - I've had MySQL lock out a table for several hours adding an index before, admittedly on a table with tens of millions of rows. Of course this is only an issue for busy production sites, but it's always wise to plan for scalability.
glenn
|
10036
posts
|
PythonAnywhere staff
|
rcs1000
|
311
posts
|
