Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,193,910 members, 7,952,664 topics. Date: Wednesday, 18 September 2024 at 08:35 PM |
Nairaland Forum / Science/Technology / Webmasters / MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql (6066 Views)
Creating An Alumni Site With Php And Mysql / Designing A Site With Php And Oracle Tutorial (Oracle 10G) / Controlling Cpanel With Php Scripts To Create Email, Dbase, Subdomains: (2) (3) (4)
MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:12pm On Feb 26, 2009 |
Iight guys. This is supposed to be an article, but questions can be asked though. Minimal requirement for this thread is that, you MUST be able to create mysql databases and integrate them with php. It is actually a thread meant for advanced users but i will entertain any questions asked relating to it no matter how foolish the question is. For starters you may want to read this foreign article - http://www.mikebernat.com/blog/Setting-Up_a_Relational_Database_in_MySQL So to continue with the intro. I am just doing a repeat broad-cast in 9ja language. Many of you developers just create database and just carry go like that, and dont even care whether the database type is MyIsam or InnoDb. If you are one of this, please read on rather than get annoyed with me. Yes, you can say database is database, but if you care about efficiency like this. If i create the database of nairaland, i can wipe off all the topics and posts i have ever created on nairaland using a one line common command like this: delete from users where username=dhtml - familiar command huh? So, i am not here to teach you basic SQL codes but rather to give you some insight about creating clean and efficient databases that will reduce your php codes greatly - think about it as a smarter way to lay out your databases. |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:22pm On Feb 26, 2009 |
There are what we call storage engines in mysql. They are rather like plug-ins that do the actual storage of your tables. The common types of storage engines are as follow: -> MyISAM -> InnoDB -> Merge -> Heap -> Memory -> BDB -> ISAM Please dont be bored 'cos i know some of you may be wondering WTF i am talking about, while some are just impatient to find codes to copy - codes will come eventually but explanation first so that you will know how to use the codes. Codes are not the aim of this thread - but rather table creation and optimization. So MyISAM and InnoDb are the most commonly used storage engines - read all about it - http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:37pm On Feb 26, 2009 |
Now our relational database at last using InnoDb. So here is how i go about it. Create a dbase - classDemo Create a table called users. fields: id - autoincrement primary key username - varchar(255) users.sql CREATE TABLE `users` ( Then you will now go to operations on phpmyadmin, that place we usually rename database and all that. You will now find the option to change your table to innodb. The sql code directly will be CREATE TABLE `users` ( So next table will now be the profile table. Notice that while creating tables in phpmyadmin it will usually give you the option to change the dbase storage engine. Default is usually MyISAM. profile.sql CREATE TABLE `profile` ( So here is what i have done so far.
|
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:48pm On Feb 26, 2009 |
Now the tables have been created, let us now make them relational. Explanation: id on users is autoincremented, so let us now register the following users: omnipotens, yawatide users.sql CREATE TABLE `users` ( So the id of omni is 1 while yawa is 2. This ids are unique too. So i now want to tie this ids to the profile table in a relationship model. So, i now go back to the profile table and click on user_id and select the option to make it an indexed field. Image attached below. Then you will now click on relation view (this is never available in MyISAM) - so dont be bothered that you have never seen it before. Let us see some screenshots before we continue.
|
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:55pm On Feb 26, 2009 |
Now go to the relation view and link the user_id of profiles to the id of users table. Dont be confused, follow the screenshot below. Then we continue from there.
|
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 9:04pm On Feb 26, 2009 |
Now lemme explain what we have done in essence: 1. You cannot add an entry to the profile table without specifyin the user_id and the user_id must exist in the users table too as pid. 2. When you delete a particular user from the users table, all the comments that carry his user_id will be deleted at once. If you are confused, please forget about that and continue reading. We are talking practicals as alwayz. profile.sql CREATE TABLE `profile` ( So let us now insert records into the profile table. Notice i entered the user_id of omnipotens in the screenshot. If you specify the user_id that does not match any users.id that query will be rejected, you can try it with a value of 0 instead of 1 or 2, valid.php $sql = 'INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'1\', \'Hi Y\'\'all, i am omnipotens, i got here first.\');'; invalid.php $sql = 'INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'0\', \'Hi Y\'\'all, i am omnipotens, i got here first.\');';
|
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 9:10pm On Feb 26, 2009 |
INSERT INTO `profile` (`id`, `user_id`, `comment`) VALUES (NULL, \'1\', \'Hi Y\'\'all, i am omnipotens, i got here first.\') full profile table will be CREATE TABLE `profile` (
|
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 9:14pm On Feb 26, 2009 |
Notice that the user_id contains only 1 and 2. Specifyin the required users, So to delete all the entries of omnipotens. All you need do is: delete from users where id='1'; or whatever. Once that row is deleted, all the user_id that have same value as users.id will be deleted automatically. That method of linking the profiles with the users table can be employed to tie more tables together. This is one of the powers of innodb aside the row locking stuff. And this is the end of my article, good night folks. Gotta get back to work. |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Seun(m): 12:17am On Jun 12, 2009 |
Thanks for the tutorial. |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 6:01am On Jun 12, 2009 |
Hi chief, you are welcome as alwayz. . . |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by segsalerty(m): 9:19am On Jun 12, 2009 |
nice thread , didnt know this exists before, bt , cool thanks as always DHTML |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by smartsoft(m): 5:32pm On Jun 12, 2009 |
OMO i don 4get all dis thing ooooooooooooooooooooo colours don take ova me finish |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nobody: 8:46pm On Jun 12, 2009 |
smartsoft:Just lyke codes don scatter my head finish! @segsalert, i have lost track of my useful threads on webmasters sef, i will try rearrange then again someday. Thanks to omnipotens for helping me bookmark some of them here, and i tried to link some of them together too. . . |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by dryegg: 2:25am On Jan 05, 2011 |
you people should check out thislink it explains quite a bit http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/notsowellknown-differences-between-myisam-and-innodb-mysql-server-storage-engines.php |
Re: MyISAM Vs InnoDb - Featuring Relational Dbase With Php / Mysql by Nmeri17: 10:30pm On Mar 05, 2016 |
this was before you discovered the troll imprisoned inside you.....and subsequently, set it free |
(1) (Reply)
Verified Paypal Accs For Sell @ N5000 / Firefox Tabbed Browsing Is Helping Scammers In Africa? / 7 Ad Networks To Make Money On Your Blog
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 29 |