0

1) In mysql engines, MyISAM Is Better Or InnoDB ?

2) i want programming a cms that it could add a post with multi category how i must design my database to have a better performance with a few query ?

in php how can list posts which category for example = 1 ?

thank you

DJafari
  • 12,955
  • 8
  • 43
  • 65

2 Answers2

1

1) If you need foreign keys relations on DB level use InnoDB else use MyISAM

2) You can store all categories in one table with schema like that 

create table categories (
Category_ID int NOT NULL,
ParentCategory_ID int NOT NULL default 0,
CategoryName varchar(150)
);

with Category_ID primary key

3)

$sql = select * from posts where category_id = 1;
mysql_query($sql);

edit : Schema of post table (example)

create table posts (
    Post_ID int NOT NULL,
    Category_IDs varchar(50) NOT NULL,
    POSTDescription varchar(1000),
    POSTTime int NOT NULL
)

Post_ID is primary key of posts table.

note the Category_IDs is now varchar store value of categories in it like 1,2,3 if your post belongs to cateory 1,2 and 3.. and for deleting all posts belonging to category 1 you will run following query

$sql = DELETE
FROM `posts`
WHERE FIND_IN_SET( '1', `Category_IDs` ) >0;
Muhammad Ummar
  • 3,541
  • 6
  • 40
  • 71
  • no no ! i have a category table look like to your categories table, i want a table for posts_categories to store all categories of a post, – DJafari Apr 15 '11 at 11:31
  • about question 1, when need to foreign key ? – DJafari Apr 15 '11 at 11:32
  • you mean a post can belong to mutiple categories in your case? and you need foreign key when you want to link two tables. e.g. you want your posts to be linked with their categories then you will put `Category_ID` as a foreign key in posts table. – Muhammad Ummar Apr 15 '11 at 11:45
  • is possible that give a full structure for this ? – DJafari Apr 15 '11 at 11:47
  • for example, if for post_id = 1 have 2 category from my 5 category, how this 2 category store ? – DJafari Apr 15 '11 at 11:59
  • hmm in that case the above schema will not work, some change in schema will be required for post table let me update my above post. – Muhammad Ummar Apr 15 '11 at 12:18
  • @Ummar, you are great, thank you, let me to test find_in_set speed in big DataBase To test Optimization and query time length . – DJafari Apr 15 '11 at 13:06
  • @Davood, you are wellcome, one thing to tell you that if you need maximum performance for `find_in_set` then change the column type of `Category_IDs` to `SET`. As `find_in_set` perform bit operation on sets. – Muhammad Ummar Apr 15 '11 at 13:13
  • Thank you Ummar, but range of categorys id is unknown, it's mean cannot change varchar to set, have any idea ? – DJafari Apr 15 '11 at 13:21
  • yes, its an issue but you can give arbitrary range for set, e.g. the Maximum expected value like 1000... not a good approach but a work arround.. – Muhammad Ummar Apr 15 '11 at 13:32
1

There is no definitive answer as it depends on your requirements but, lots of people will have already mentioned things such as:

  • innodb has row level locking vs. myisam table locking so innodb can handle more concurrent requests.
  • innodb is transactional so inserts will generally be slower than myisam
  • innodb is a proper RDBMS engine so supports referential integrity (transactions ACID bla bla bla)
  • innodb is more reliable than myisam
  • myisam is faster than innodb for reads (myth)
  • myisam tables have smaller footprints than innodb ones (myth)

However not many people will mention innodb clustered primary key indexes and how a well designed innodb table will easily out perform an equivalent myisam one because of this.

Here are two links explaining clustered indexes:

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

Also, have a look at the following examples and articles (second example may have particular relevance)

Hope this helps :)

Community
  • 1
  • 1
Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • Referential integrity has nothing to do with whether a DBMS (much less a database) is a relational one or not. MySQL is based on SQL, which means it is not truly relational. Using InnoDB doesn't make MySQL any more or less an RDBMS than it is when using MyISAM. – nvogel Apr 15 '11 at 12:15
  • edited post to include (not a proper RDBMS - ACID bla bla bla bla) but it's a read performance based answer. – Jon Black Apr 15 '11 at 12:54
  • @Ummar, I was correcting potentially misleading information in the answer. I wasn't criticising the question in any way and I don't know why you would get the impression that I was. – nvogel Apr 15 '11 at 13:04
  • no worries guys - chill, i glossed over a few facts in my haste to cut to the chase namlely CLUSTERED INDEXES. – Jon Black Apr 15 '11 at 13:06