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

Improve this question

2 Answers 2

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

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,
about question 1, when need to foreign key ?
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.
is possible that give a full structure for this ?
for example, if for post_id = 1 have 2 category from my 5 category, how this 2 category store ?
|
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 :)

Improve this answer

4 Comments

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.
edited post to include (not a proper RDBMS - ACID bla bla bla bla) but it's a read performance based answer.
@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.
no worries guys - chill, i glossed over a few facts in my haste to cut to the chase namlely CLUSTERED INDEXES.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.