RE: Backing up/Creating new database
The easiest way is simply to copy the files. Go to the directory that mysql was installed to. There should be a subdirectory called data. Under that should be a subdirectory named after your database. You can simply copy that entire subdirectory to a mysql server and the database will be copied over. Want to change the name of the database? Change the name of the subdirectory. -Rob -Original Message- From: PR [mailto:[EMAIL PROTECTED]] Sent: Monday, May 13, 2002 7:47 AM To: [EMAIL PROTECTED] Subject: Backing up/Creating new database Hi, I have database developed on my laptop. What is the quickest way to backup the database and restore it on the server? Can I restore the database as empty database? Howa bout creating SQL statements based on the database structure? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Massive tables - 2 Queries better than join?
Hello, I am trying to understand a fundamental concept here. Lets say we have two tables: CREATE TABLE codewords (id INT UNSIGNED not null AUTO_INCREMENT, word VARCHAR (60) not null , fkey_humanword INT UNSIGNED not null , PRIMARY KEY (id), INDEX (id), UNIQUE (id)) CREATE TABLE humanword (id INT UNSIGNED not null AUTO_INCREMENT, word VARCHAR (60) not null , PRIMARY KEY (id), INDEX (id), UNIQUE (id)) A 'codeword' is put into a script, which gets the corresponding 'humanword' and echoes it to the screen. So lets say the codeword 'bose' corresponds to the humanword 'best speakers'. Now, I know what I am about to describe is poor RDBMS design but it helps me understand a concept: Let's say that in the table 'codewords' there can be multiple instances of the same codeword. Perhaps the codeword 'bose' appears 45,000 times. However, all 45,000 instances of the codeword 'bose' have the same fkey_humanword (they correspond to the same humanword.) Why would there be 45,000 duplicate entries? Please just accept that for some reason all the duplicates need to be there. Now, lets say I wanted to display every possible humanword. I could just do SELECT word FROM humanword. However, lets say that I need to make the query select from the table 'codewords'. So I would do something like SELECT h.word FROM codewords AS c LEFT JOIN humanword AS h ON h.id=c.fkey_humanword GROUP BY h.id So this way it will join the two tables but only display distinct values. Now this is joining two massive tables though, say 1 million rows each, so would it be faster to execute two queries such as: SELECT c.fkey_humanword FROM codewords AS c GROUP BY fkey_humanword Then loop through each resulting fkey_humanword, store it in a variable called '$varname', and have the second query be SELECT w.word FROM humanword WHERE id=$varname Everything tells me the JOIN is the best way to go. If the tables are properly indexed it should scream. However, I am wrestling with understanding the fundamentals of how the join with a GROUP BY clause works. Does MySQL select a row from the first table than scan the entire second table for the JOIN, then go on to second row of the first table and perform the join for that row, and on and on? Or does MySQL first perform the GROUP BY clause on the first table, take the resulting rows and than perform the JOIN on the small amount of grouped rows left? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
One field or numerous datatype-specific tables?
I am having a problem determining the best SQL DB schema to use. I have a table which stores attributes on homes, attributes such as square footage, price, or includes pool. Now the problem I am having is how to store the actual value of the attributes. An attribute like square footage or price can simply be stored as a BIGINT. However, an attribute like includes pool is a boolean and I don't want to make a bloated table by storing a 1 or 0 in a BIGINT field. Plan 1 CREATE TABLE homes (id BIGINT UNSIGNED not null , fkey_attrib BIGINT UNSIGNED not null , value BIGINT UNSIGNED not null , PRIMARY KEY (id), INDEX (id), UNIQUE (id)) Let's say I wanted to show all properties that had a price of less than $200,000. Let's say that the price attribute is attribute number 32. I would execute the following: SELECT id FROM homes WHERE (fkey_attribid=32 value20) Now let's say I wanted to show all properties that had a pool and the includes pool attribute was attribute number 12. SELECT id FROM homes WHERE(fkey_attribid=12 value=1) The problem I am having is in defining the field value. Since it is going to store numbers that will be larger than the ranger for UNSIGNED INT, I have to use a BIGINT datatype. It just seems like a waste to me, however, to have a BIGINT datatype used for boolean attributes (such as includes pool). Plan 2 The alternative I came up with is to make the field value be relational. I would create several other tables such as: CREATE TABLE homes (id BIGINT UNSIGNED not null PRIMARY KEY (id), INDEX (id), UNIQUE (id)) CREATE TABLE homes_bigint(id BIGINT UNSIGNED not null , fkey_attrib BIGINT UNSIGNED not null , value BIGINT UNSIGNED not null , fkey_homes BIGINT UNSIGNED, PRIMARY KEY (id), INDEX (id), UNIQUE (id)) CREATE TABLE homes_boolean(id BIGINT UNSIGNED not null , fkey_attrib BIGINT UNSIGNED not null , value TINYINT UNSIGNED not null , fkey_homes BIGINT UNSIGNED, PRIMARY KEY (id), INDEX (id), UNIQUE (id)) Now it would fall on the responsibility of my script (which I am fine with) to determine which attribute pairs up with which table. Let's say I wanted to show all properties that had a price of less than $200,000. Let's say that the price attribute is attribute number 32. I would execute the following: SELECT id FROM homes AS h LEFT JOIN homes_bigint AS hb ON homes USING hb.fkey_homes=h.id WHERE (hb.fkey_attribid=32 hb.value20) Now let's say I wanted to show all properties that had a pool and the includes pool attribute was attribute number 12. SELECT id FROM homes AS h LEFT JOIN homes_boolean AS hb ON homes USING hb.fkey_homes=h.id WHERE (hb.fkey_attribid=32 hb.value=1) So the question I am debating is if I should put everything in just one table (in which case boolean values would be stored in the same field as BIGINT values) or if I should have separate tables that are streamlined to particular datatypes. Would having everything in one table be faster? Or could I simply optimize the numerous datatype-specific tables in plan 2 and be just as fast? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can you skip ORDER BY get rows back in inserted order ?
Sam, I am constantly coding with PHP and MySQL. As far as: Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to o ORDER BY so that FILESORT or TEMPORARY can be avoided ? I have learned to let MySQL do anything DB related. Generally, whenever you can have MySQL do the work you are better off than letting PHP do it - unless you need some type of error checking or logging. Rob -Original Message- From: Sam Iam [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:53 AM To: Paul DuBois Cc: MySQL List Subject: Re: Can you skip ORDER BY get rows back in inserted order ? Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. Is this because the index cache in ram may not be loaded in order ? You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. My rank column is the order that I want them back in. Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ? thanks, - Sam. PS : I bought your book by the way. Great read. On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote: At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC. For some reason I get the data back in a different order. I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO. Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
