CODEWITHSUNDEEP
phpmysqldatabase-designdatabase-optimization
DJafari
DJafari

Reputation: 13535

2 question about better optimize database design

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

Upvotes: 0

Views: 157

Answers (2)

Jon Black
Jon Black

Reputation: 16559

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 :)

Upvotes: 1

Muhammad Ummar
Muhammad Ummar

Reputation: 3631

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;

Upvotes: 1

Related Questions