Order to run ANALYZE, OPTIMIZE and CHECK
Hi! Given the commands like ANALYZE, OPTIMIZE and CHECK, what is the preference order to execute these commands? We would like to schedule and execute these commands every month... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reset (or Defrag) the AUTO_INCREMENT columns
Yes, I agree on what you have described. However, what should we do when the value is reaching the maximum? To alter the data type to a bigger one? On 6/14/06, Daniel Kasak <[EMAIL PROTECTED]> wrote: wolverine my wrote: > May I know if there is any way we can reset (or defrag?) the values You shouldn't do that. It's much easier for you as a database administrator if these values are left up to MySQL. What happens, for example, if you restore from a backup that has rows which you recently deleted? You'll have a number of records ( possibly many ), all with the same primary key. How do you figure out which record is the oldest one, which is the 2nd oldest one, which is the current one, etc? What happens if you have records in a related column that were referring to this primary key? You've got a big mess! Also, there's no such thing as 'defrag'ing an auto_increment column. The space left when you delete a row will be taken by another record when MySQL sees fit. If you absolutely must have a continuous stream of numbers for your primary key, then don't use an auto_increment column. Instead, use a numeric column and write some code to create your next primary key value. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reset (or Defrag) the AUTO_INCREMENT columns
Hi! I have the following tables and the data, CREATE TABLE category ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY name VARCHAR(50) NOT NULL ); CREATE TABLE user ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, category TINYINT UNSIGNED REFERENCES category(id) ); SELECT * FROM category; +++ | id | name | +++ | 1 | Classic| | 2 | Gold | | 5 | Platinum | | 6 | Blacklist | +++ SELECT * FROM user; +++--+ | id | name | category | +++--+ | 2 | John | 1| | 3 | Mark | 2| | 5 | Kenneth| 5| | 6 | Sammy | 6| | 8 | Jane | 5| +++--+ Based on the above, the values of both ids are defragmented. The category.id 3 and 4 are deleted and the user.id 1, 4 and 7 are deleted. May I know if there is any way we can reset (or defrag?) the values so that they look like the following? SELECT * FROM category; +++ | id | name | +++ | 1 | Classic| | 2 | Gold | | 3 | Platinum | | 4 | Blacklist | +++ SELECT * FROM user; +++--+ | id | name | category | +++--+ | 1 | John | 1| | 2 | Mark | 2| | 3 | Kenneth| 3| | 4 | Sammy | 4| | 5 | Jane | 3| +++--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL naming convention?
Hi! When writting a SQL scripts, do you follow any standard SQL naming convention? Similar to what described in http://kurafire.net/articles/sql-convention Does MySQL provides such naming convention guidelines and best practices? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
