dsmythe

This would be much easier to do with your application code rather than MySQL. Just do a quick SELECT first and see if there is data in FULL. If there is, stop your script from allowing someone to add data to AM or PM, etc. Build the logic into your application and use MySQL to store your information. -Dan-

I beg to differ: " Foreign keys definitions are subject to the following conditions: * Both tables must be InnoDB tables and they must not be TEMPORARY tables. * Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same. * In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. * In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. * Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length. * If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically. " http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I do agree, however, that it is a good idea to create KEY's on the columns used in relational joins between 2 MyISAM tables, however these are not Foreign keys, neither by definition nor by function. They simply index the two columns which in turn optimizes joins as normal KEY's would. MyISAM simply does not know what a Foreign key is.

My point was that it doesn't exhibit the benefits of being a Foreign key. MyISAM will completely ignore it: mysql> create table `t1` ( -> `id` int, -> `name` varchar(10) ) ENGINE = MyISAM -> ; Query OK, 0 rows affected (0.04 sec) mysql> create table `t2` ( -> `id` int, -> `last` varchar(10) ) ENGING = MyISAM -> ; Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE t1 ADD FOREIGN KEY(id) REFERENCES `t2`(id); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table `t1`\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) As you can see, it simply creates a normal Key and does nothing to the referenced table: mysql> show create table `t2`\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `last` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) -Dan-

The *.bin files are the MySQL binary log used for Replication. It records the queries executed on the server which change the data, and generally creates a new file every time the server is restarted. In a replication environment, these logs would be transmitted to a separate MySQL server, which would replay the queries on it's own database, thereby re-creating an exact replica of the original database. For data recovery, suppose you made a backup at 1:00am and the server crashed at 7:00pm that evening. Without the bin logs you would only be able to restore your data to 1:00am. With the bin logs, you would be able to restore the database to 1:00am, and then replay the queries executed between 1:00am and 7:00pm to bring the database back to the state it was in before the crash. If you don't replicate, and you don't care about potentially losing data after a crash, then by all means delete all but the most current *.bin file. This is also most likely set in your configuration file (usually /etc/my.cnf) with a flag like "log-bin" that you can comment or remove to stop it. I wouldn't do it - but then again I wouldn't want to lose any data. If my data didn't change often I probably wouldn't care too much. It's also fairly safe to delete the old *.bin files after you make a backup, so long as you're sure you have a valid backup. You won't need to replay queries that happened before you made a backup, because they would already be in your backup. -Dan-

The primary -> foreign key relationship is only really useful when using the InnoDB storage engine. It establishes a relationship between the records who's keys match, and allows for error checking and maintenance. For instance, if you have a table full of countries, and a table full of states - you could have a primary -> foreign key relationship between the two. This could prevent you from adding states without countries, or enable you to delete a country along with all corresponding states. As long as your invoice numbers are going to be unique all the time and they're only digits (no spaces or hyphens) I don't see a benefit in having an extra column simply to have automatically incrementing identifiers. Besides, it won't have any correlation to the data being stored anyway. It would also be simpler to use your invoice number as the unique identifier, since, if you split the database into separate tables, it will make sense in all the tables to look it up by invoice number. The main definition of a primary key is a number (or string of characters, although MySQL performs better with numbers) that will always be unique, and never be deleted or re-used. Certainly an invoice number would be a good choice for a primary key. You would use it to reference the rows in the database because of the uniqueness. Primary keys are also indexed, which helps MySQL keep track of where in the database the record is, which speeds up retrieval. Instead of having to scan every row to generate your results, MySQL will know exactly where to look and what rows to scan to get your results if you reference the key in the query. Archiving would be like boxing up old data. You need/want to keep it, but you don't necessarily need it to be accessible all the time. If you had been running this database for 4 years, you would want to take all the invoices from 2004 and put them in their own table away from the main database of current records. This would leave them still available to query should you need to search back in time real far (and you would expect your query to take longer as well), but would keep them out of the current jumble to keep current activities speedy. The MyISAM storage engine offers table compression, where you can have the data optimized and compressed to save disk space, but still available to SELECT from. The downside of compressing tables would be that you can't add or modify new data. This is why you would do this with 4 year old records, for instance, because that data will no longer change. -Dan-

What version of MySQL? mysql> show create table users\G *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `userid` int(11) DEFAULT NULL, `username` varchar(50) DEFAULT NULL, `usergroup_id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table usergroups\G *************************** 1. row *************************** Table: usergroups Create Table: CREATE TABLE `usergroups` ( `group_id` int(11) DEFAULT NULL, `clan_member` enum('1','0') DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from users; +--------+----------+--------------+ | userid | username | usergroup_id | +--------+----------+--------------+ | 1 | name1 | 1 | | 2 | name2 | 2 | | 3 | name3 | 3 | | 4 | name4 | 4 | +--------+----------+--------------+ 4 rows in set (0.00 sec) mysql> select * from usergroups; +----------+-------------+ | group_id | clan_member | +----------+-------------+ | 1 | 1 | | 2 | 1 | | 3 | 0 | | 4 | 0 | +----------+-------------+ 4 rows in set (0.00 sec) mysql> SELECT userid, username FROM users -> LEFT JOIN usergroups ON users.usergroup_id = usergroups.group_id -> WHERE clan_member = 1; +--------+----------+ | userid | username | +--------+----------+ | 1 | name1 | | 2 | name2 | +--------+----------+ 2 rows in set (0.00 sec) -Dan-

In my opinion, normalizing your data will definitely speed up queries depending on the range of data you need from each invoices' row, but keep in mind it will also increase the complexity of the SQL you're going to have to write. You original question was what will happen when you get a lot of data. Primarily this is going to be influenced by the server and resources that you're giving to MySQL. A dual core server w/ 4 GB of RAM strictly dedicated to your database and optimized properly could probably handle 40 Million records + Depending on your hardware schema, My suggestion would be to archive the old invoices after n number of years, depending on how well your server can handle it. If your server can handle 3 years of invoices, then start archiving a year at a time after 3 years. Another option would be to use MySQL's partitioning capabilities (in v5.1) to automatically divvy up the invoices by date as you insert the data. This makes the archiving process easier and also provides some performance gain when you're dealing with a lot of records. -Dan-

Try something like: SELECT userid, username FROM users LEFT JOIN usergroups ON users.usergroup_id = usergroups.group_id WHERE clan_member = 1 -Dan-