472,779 Members | 2,842 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,779 software developers and data experts.

MyISAM versus Innodb - help me!!

We have developed a site where users can post blogs.

Now, each blog is stored in a separate row, in a table called
blog_table.

This single table would be the most active one and would grow to large
size in the coming years.

What is the most advisable engine for this table - Innodb or MyISAM ?

Also, I do need FULLTEXT search, and since Innodb doesn't support
FULLTEXT indexes, are there 3rd party tools that could do the indexing

? (Maybe mnoGoSearch ?)
Mike

Jul 23 '05 #1
3 2669
"siliconmike" <si*********@yahoo.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
We have developed a site where users can post blogs.

Now, each blog is stored in a separate row, in a table called
blog_table.

This single table would be the most active one and would grow to large
size in the coming years.
interesting.

you don't think there should be one table called "blog_table" with the
name of each blog ("Jenny's Cool BlogWorld") and then a seperate table
called "blog_entries" that has the actual blog entry and a reference to
which blog it should appear in (an ID from "blog_table")? I *guarantee*
this is the proper way to do this, rather than keeping everything in one
single table. For example, what if Jenny wanted to change the title of
her Blog to "Jenny's Terrible BlogWorld"? Or if Jenny wanted to change
her email address/age/sex/anything? Or if she wanted to delete a single
blog entry?
So, we're looking at three tables at minimum:

"blog_owners":

OwnerID | OwnerName | OwnerEmail
1 Sammy P. sa***@me.net
2 Jenny S. je***@example.com
4 Marvin H. ma****@home.net
"blog_table":

BlogID | BlogOwner | BlogTitle
1 2 "Jenny's Cool BlogWorld"
2 4 "Marvin's Blog o Plenty"
"blog_entries":

EntryID | BlogID | BlogEntry
1 2 "My name is Marvin. I am 11 years old."
2 1 "If my name wasn't Jenny, things would be easy"
3 1 "Today mom said, 'Jenny you are great!'"
Therefore, the table seeing the most action/size increase would be
"blog_entries".
What is the most advisable engine for this table - Innodb or MyISAM?
i can't see why you'd even consider InnoDB for this project. what makes
you think you need transactions for this project?

Also, I do need FULLTEXT search, and since Innodb doesn't support
FULLTEXT indexes, are there 3rd party tools that could do the indexing


well you've answered your own question here, dont you think. forget
third-party tools. again, there is no reason from what you've given as
to why InnoDB should even be considered.
Instead of leaping in with "what engine should I choose", you should make
sure your database schema is as good as it can be. I would say that the
amount of customizable web projects that run smoothly off of a single
table in a database would be close to zero.
Jul 23 '05 #2


Good Man wrote:
"siliconmike" <si*********@yahoo.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
We have developed a site where users can post blogs.

Now, each blog is stored in a separate row, in a table called
blog_table.

This single table would be the most active one and would grow to large
size in the coming years.
interesting.

you don't think there should be one table called "blog_table" with the
name of each blog ("Jenny's Cool BlogWorld") and then a seperate table
called "blog_entries" that has the actual blog entry and a reference to
which blog it should appear in (an ID from "blog_table")? I *guarantee*
this is the proper way to do this, rather than keeping everything in one
single table. For example, what if Jenny wanted to change the title of
her Blog to "Jenny's Terrible BlogWorld"? Or if Jenny wanted to change
her email address/age/sex/anything? Or if she wanted to delete a single
blog entry?
So, we're looking at three tables at minimum:

"blog_owners":

OwnerID | OwnerName | OwnerEmail
1 Sammy P. sa***@me.net
2 Jenny S. je***@example.com
4 Marvin H. ma****@home.net
"blog_table":

BlogID | BlogOwner | BlogTitle
1 2 "Jenny's Cool BlogWorld"
2 4 "Marvin's Blog o Plenty"
"blog_entries":

EntryID | BlogID | BlogEntry
1 2 "My name is Marvin. I am 11 years old."
2 1 "If my name wasn't Jenny, things would be easy"
3 1 "Today mom said, 'Jenny you are great!'"
Therefore, the table seeing the most action/size increase would be
"blog_entries".

Oh well, your 'blog_entries' is my 'blog_table', but your mail confirms
that what I'm doing is right.

What is the most advisable engine for this table - Innodb or MyISAM?


i can't see why you'd even consider InnoDB for this project. what makes
you think you need transactions for this project?

Also, I do need FULLTEXT search, and since Innodb doesn't support
FULLTEXT indexes, are there 3rd party tools that could do the indexing


well you've answered your own question here, dont you think. forget
third-party tools. again, there is no reason from what you've given as
to why InnoDB should even be considered.

Well I was also considering factors like
increased speed due to row level locking in Innodb
and other factors (unknown to me) where my choice of Innodb would be
justified even though I am not using transactions..

Jul 23 '05 #3
"siliconmike" <si*********@yahoo.com> wrote in
news:11*********************@g49g2000cwa.googlegro ups.com:
Well I was also considering factors like
increased speed due to row level locking in Innodb
and other factors (unknown to me) where my choice of Innodb would be
justified even though I am not using transactions..


you won't notice it - go MyISAM all the way for this project.
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: jackson marshmallow | last post by:
I have just recreated several (large) tables in the database as InnoDB and reloaded them using my client application... These tables used to be MyISAM. I haven't tried playing with any InnoDB...
0
by: Marek Kotowski | last post by:
I am new to MySQL and this is a simple question: what table type to choose: MyISAM or INNODB? I need a referential integrity to be checked, so I know INNODB is the only choice, at least...
0
by: Stefan van Roosmalen | last post by:
I want to use constraints, so I will have to use innodb tables (according to the website mysql.com). But, what is the difference between myisam and innodb tables ?? And why is there a comment...
3
by: Mike Ho | last post by:
I'm wondering if there is a way to retrieve Foreign Key declaration information on both MyISAM and InnoDB tables. Of course, I understand that MyISAM doesn't support Foreign Keys, however, it...
3
by: Reuben Pearse | last post by:
Hi all, I've just converted the tables in a big database (approx 27 million records) from MyISAM to InnoDB. When I was using MyISAM I backed up the database by stopping MySQL and then copying...
4
by: Good Man | last post by:
Hi there I have a database with about 20 or so tables, maybe a few thousand rows in each. I am starting to do more complex things with my insertions etc, and I want to start to use...
2
by: crescent_au | last post by:
I've read articles and postings about MyISAM vs InnoDB but I am still a bit unsure about which storage engine to use for my new project. I am developing a website in PHP/MySQL, which includes...
7
by: fjm | last post by:
Hi all, I'm new so please be forgiving if my question appears stupid. Thanks to Motoma and his fine class script, I was able to put together a very nice looking template. I am using mysql...
2
by: MysqlBeginner | last post by:
Is this possible to have a MyISAM table on master server and change its storage engine (to InnoDB) on slave server in a replication environment? If this is possible, what should I consider before...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.