Re: InnoDB doubles size when converting from MyIsam

2009-09-13 Thread Arthur Meeks Meeks 
2009/9/13 Dan Nelson dnel...@allantgroup.com

 In the last episode (Sep 12), Arthur Meeks Meeks said:
  I have a database with about 1000 tables and 150GB. I have done a simple
  for f in $(cat tables); do mysql -uuser -ppassword database_name -e
  alter table $f engine=InnoDB; ; done
 
  I took about 3 hours and everything went fine, but I just realised that
  the same database in another server in myisam is about 150GB and in the
  server where I converted it to innodb it's about 350GB.
 
  Is that normal?

 Yes; innodb tables will require quite a bit more space than myisam, because
 the table data itself is stored as an index (which means each page will be
 between 50% and 94% full), and because each row needs extra space to hold
 transaction and locking info.

 http://dev.mysql.com/doc/refman/5.1/en/dynamic-format.html

 http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html
 http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-record.html


Thanks a lot Dan, now it makes sense.

A

Re: database design

2009-09-13 Thread Mogens Melander 
Well, if you have a fixed number of article types, then maybe.

If there is a chance of more types being added later, then no.

Are you planning to hard code selection lists in your front end,
or would you like to retrieve data from sql ???

Maybe a read up on Database Normalization is due:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

On Sat, September 12, 2009 07:19, AndrewJames wrote:
 thank you all, i think

 You probably wouldn't need Article_Type table if you're going to store
 Article_Type value directly.

 is my answer.


 --
 From: Kyong Kim kykim...@gmail.com
 Sent: Saturday, September 12, 2009 8:22 AM
 To: Arthur Fuller fuller.art...@gmail.com
 Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames
 andrewhu...@gmail.com; mysql mysql@lists.mysql.com
 Subject: Re: database design

 A) You would probably want to populate the Article.Article_Type column
 with Article_Type.ID. You probably wouldn't need Article_Type table if
 you're going to store Article_Type value directly.

 I would also consider the use of natural primary key vs surrogate
 primary key. We've seen good results with primary key lookups on large
 tables (especially creating grouped subsets of data)

 If you imagine your data set growing fairly large, you should take a
 stab at projecting your workload to determine whether you would want
 to optimize access speed vs insert.

 For example, if you will be searching the article table by uid, you
 might want to cluster the data by uid so all related articles will be
 stored next to each other.

 Kyong

 On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com
 wrote:
 I agree with Claudio. You have your design correct. The only other thing
 you
 need is the uid qualifier. Presumably you are using PHP or some other
 front
 end to present your data. Your front end would request the user's name
 and
 password, saving the uid in a variable and then issuing the select with a
 WHERE clause that passes the uid in:
 select * from articles A left joing article_types AT on A.article_type =
 AT.Arcticle_types_id WHERE A.uid = insert your variable here

 hth,
 Arthur

 On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni
 claudio.na...@gmail.comwrote:

 A.J., It sounds good to me!
 You can be a little confused but you did it well,
 It seems you have all you need there.

 A) Yes
 B)  select * from articles A left join article_types AT on
 A.article_type =
 AT.article_types_id

 Claudio




 2009/9/11 AndrewJames andrewhu...@gmail.com

  This is a bit of a long shot, but i really need some help and or
  directed
  to the best reading resources.
 
  as i begun building my database (as i went along), i now realise i
  have
 to
  stop coding and sit back and design the database properly before i can
  go
  on.
 
  However i am still unable to wrap my head around what data to put into
 what
  tables, and which columns i need to link to make the relationships.
  so
 far,
  here is what i have.
 
  TABLES:
 
  users
  -uid(pk)
  -username
  -password
 
  articles
  -article_id(pk)
  -uid(fk)
  -article_type(fk)
  -article_subject
  -article_body
 
  article_types
  -article_types_id(pk)
  -article_type
 
  So i want the user to be able to login and add articles.
 
  I then want to be able to view all the articles the user has
  submitted.
 
  So in my understanding i need to link the users.uid(pk) to the
  articles.uid(fk) (so i know which user the article belongs to, please
  correct and update me if i am wrong)
 
  I am stuck at this point.
 
  A) Have i created the right tables and columns for each table, AND
  B) How do i link the articles.article_type to articles_type.type? (IF
  in
  fact that is even the correct linkage)??
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
 
 


 --
 Claudio




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk


 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.



-- 
Later

Mogens Melander



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Mysql prepare statement

2009-09-13 Thread Manasi Save 
Hi All,

I have a query about prepare statement of mysql. I want to use prepare
stmt for insert-update-delete with a dynamic dbname,So can anyone provide
any input how i can make prepare stmt and then after that provide a value
at runtime. for example :-

prepare stmt from Concat('Insert into ',?,'.IgnoreContact (testid)',
'Select ',?,',';');

Below is the procedure where i am preparing stmt at runtime. whereas i
want to pass the values at runtime.

If anyone can provide any information and links where i can find
information abt mysql prepare stmt from stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_Test` $$
CREATE definer=`testus...@`localhost` PROCEDURE `SP_Test`(InputTestID
Bigint, DBName Varchar(100))
BEGIN

SET @stmt = Concat('Insert into ',DBName,'.IgnoreContact (testid)',
'Select ',InputTestID,';');

Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;

END $$

DELIMITER ;

Thanks in advance.

-- 
Regards,
Manasi Save
Artificial Machines Pvt Ltd.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org