database efficiency

  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

I've done quite a lot of work with databases and SQL, but all have been off-line and in single user environments. Because of this I can normalize an relational database (3rd NF only I'm not faffing with that 5th NF malarky) in my sleep, but I know nothing of database efficiency, which is now a lot more important because I'm moving into internet based database solutions.

I've done a couple of googles, but was wondering if any of you guys no of some good quality articles on keeping database driven scripts quick and efficient. Or have you got some general hints or advice. It's a kind of free-for-all :D

Thanks
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

MySQL and SQLite are the two fastest databases aroudn and are the only ones worth using.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Okay bit of advice from me (for what it is worth)

A: Dont do SELECT * FROM statements, rather specify the fields you looking for

B: TEST ALL your result sets before executing queries against them.

C: use @mysql rather than mysql when releasing the site (allows Mysql to fail silently)

D:Set error reporting to 0 after development and rather have a mail sent to you containing the actual error

E:Look into the deeper normalization (think I sit at 4th).

That is all I can thknk of now but if I come up with anything else will let you know.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Check out http://www.sitepoint.com

cool ref's
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Rabid Dog wrote:
Okay bit of advice from me (for what it is worth)

A: Dont do SELECT * FROM statements, rather specify the fields you looking for

B: TEST ALL your result sets before executing queries against them.

C: use @mysql rather than mysql when releasing the site (allows Mysql to fail silently)

D:Set error reporting to 0 after development and rather have a mail sent to you containing the actual error

E:Look into the deeper normalization (think I sit at 4th).

That is all I can thknk of now but if I come up with anything else will let you know.


Well as it transpires I normalize to 4NF (I actually got round to deciphering the bloody definition) anyway and would naturally do 5th if I ever got any cyclic dependancies (something I have never had need for). I've "never bothered" with these two before because I never understood what they <i>mean</i>, but I was doing it instinctively all along :lol:

The only other thing I could find was adding indexes, including multi column indexes, to non-key fields. I always thought you could only index keys but I was wrong.

Thanks for the advice.
  • Rogue Forces
  • Beginner
  • Beginner
  • User avatar
  • Posts: 51
  • Loc: Kentucky

Post 3+ Months Ago

This may go without saying, but if you're moving over to enterprise apps make sure you use stored procs instead of "in-application" SQL. It increases security, efficiency, and scalability and makes the code more maintainable. I use Oracle and SQL Server but I do know that the release of MySQL 5.0 supports at least some minor stored procs.

http://www.databasejournal.com is a great site with information regarding many databases.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Well there is another hidden secret in MySQL ( or is it that I'm just slow ) but alot of people I have spoken to (that only work in the MS SQL arena) say that MySQL doesn't support foreign key constraints but it DOES! maybe not as well as MS SQL but they work.

Also look at wrapping your SQL executions in transaction type blocks
ie

start transaction
sql1
sql2
sql3
execute query
test if query was successful
commit changes

Also look into the differences between InnoDB and MyISAM table types ( can only apply foreign key constraints to InnoDB table types)

One other thing I suggest is with each project you do try building the PHP code in a way that you can re-use it across different projects. I only realised this after the third one and found myself rebuilding code that could have effectively been used over and over. (That is how I came up with my own mini-cms, plugs into any mysql db, few constant changes and bang - you have a user friendly gui!)

And one other thing - do yourself a favour:
Build a open and close db routine. Here is one I use alot

openDB
PHP Code: [ Select ]
 
function openDB(){
 
      $db = @mysql_connect(server, user,password);
 
      if ($db) {
 
         $db_err = @mysql_select_db("presence_co_za",$db);
 
         if ($db_err) {
 
            Return $db;
 
         }else{
 
            $db_connect_err = base64_encode(mysql_error());
 
            header("Location: lib/error.php?err=$db_connect_err");
 
         }
 
      }else{
 
         $db_connect_err = base64_encode(mysql_error());
 
         header("Location: error.php?err=$db_connect_err");
 
      }
 
   }
 
 
  1.  
  2. function openDB(){
  3.  
  4.       $db = @mysql_connect(server, user,password);
  5.  
  6.       if ($db) {
  7.  
  8.          $db_err = @mysql_select_db("presence_co_za",$db);
  9.  
  10.          if ($db_err) {
  11.  
  12.             Return $db;
  13.  
  14.          }else{
  15.  
  16.             $db_connect_err = base64_encode(mysql_error());
  17.  
  18.             header("Location: lib/error.php?err=$db_connect_err");
  19.  
  20.          }
  21.  
  22.       }else{
  23.  
  24.          $db_connect_err = base64_encode(mysql_error());
  25.  
  26.          header("Location: error.php?err=$db_connect_err");
  27.  
  28.       }
  29.  
  30.    }
  31.  
  32.  

nad then to close it
PHP Code: [ Select ]
 
function closeDB($db_conn){
 
      $db_err = @mysql_close($db_conn);
 
      if ($db_err) {
 
         $db_conn = null;
 
      }else{
 
         $db_connect_err = base64_encode(mysql_error());
 
         header("Location:error.php?err=$db_connect_err");
 
      }
 
   }
 
 
  1.  
  2. function closeDB($db_conn){
  3.  
  4.       $db_err = @mysql_close($db_conn);
  5.  
  6.       if ($db_err) {
  7.  
  8.          $db_conn = null;
  9.  
  10.       }else{
  11.  
  12.          $db_connect_err = base64_encode(mysql_error());
  13.  
  14.          header("Location:error.php?err=$db_connect_err");
  15.  
  16.       }
  17.  
  18.    }
  19.  
  20.  


the error messages are for development purposes so when you launch it you could just replace the header call to an email call that sends you the error and display a "sorry 'bout that" type message
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Rabid Dog wrote:
MySQL doesn't support foreign key constraints but it DOES!

........

Also look at wrapping your SQL executions in transaction type blocks


Hmmm, can't say I know what either of those mean, but I'll makes sure look them up next week :lol: Is the second one about doing several queries at once kinda thang?

Rabid Dog wrote:
One other thing I suggest is with each project you do try building the PHP code in a way that you can re-use it across different projects.

And one other thing - do yourself a favour:
Build a open and close db routine. Here is one I use alot


Lol, I've done about 6 BIG database projects using Visual Basic *shudders* and an external access file. As far as I'm concerned, the code for connecting to the database is:

Code: [ Select ]
CF_OpenTheDatabase()
CF_CloseTheDatabase()
  1. CF_OpenTheDatabase()
  2. CF_CloseTheDatabase()


Lol, I have no idea what the real commands are, I just always plug'n'play that one :D There's also a couple of other handy functions in that module as well. I only ever code modularly if I can. It's a bitch that my current project at work is so weird, it's really hard to split into modules, the whole system is a nightmare and I hate it with a passion :twisted: It's one of those jobs you just wish you could ditch and be done with.

enough of that now!

Thanks again for the tips

Post Information

  • Total Posts in this topic: 8 posts
  • Users browsing this forum: No registered users and 101 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.