Tag: database

MySQL for Beginners – Its Important Features and Advantages

MySQL logoBy Eric Datu Agustin

MySQL for beginners requires you to first know Structured Query Language (SQL), a general query language, through which you can extract, sort, update, delete and insert data. With SQL, you can manipulate data as you used relational database management systems (RDBMS) such as MySQL, Access, Ingres MicrosoftSQL Server, PostgresSQL, Oracle, Sybase, etc.

mysql-version-history

MySQL versions

Structured Query Language (SQL) has full support from the American National Standards Institute (ANSI), which has laid down certain rules for its use. So, when you want to build a strong foundation in SQL, you can choose from one of the aforementioned RDBMS that is specific for your use. As a caveat, you should have to consider three primary factors before you opt for a database system, as follows: the platform on which you work, what you want to achieve and your finances.

As you may know, all the important and common SQL statements are supported by any of these RDBMS; however, each RDBMS has its own set of proprietary statements and extensions. In layman’s terms, a database stores or housed your data files (such as names and emails of your friends). If you use small programs such as Perl for your database, your information is displayed and your data analyzed based on the program’s search criteria features.

Some of the important features of MySQL for beginners are, as follows:

a) It is very fast and much reliable for any type of application;

b) Its command line tool is powerful that it can run SQL queries against database;

c) It supports binary objects and indexing;

d) It allows changes to structure of table while server is running;

e) It has a wide user base with fast thread based memory allocation system;

f) Its code is tested with different compilers;

g) It is written in C and C++ language;

h) It is available as a separate program for use in a server/client network environment;

i) It is available for most Unix operating platform;

j) It is available for window operating system window 98 and 95, and window NT;

k) Its programming libraries for C, Delphi, Java, PHP, Python, etc. are available to connect to MySQL database; and,

l) A lot more.

In addition to the above, some of the advantages using MySQL are, as follows:

a) Its source code is available and can even be recompiled;

b) It supports more than twenty different platforms, which include Linux distribution Mac OS X, Microsoft Windows and Unix;

c) It is considered one of the world’s most popular open-source database system;

d) It is high performing and very reliable RDBMS where it can store many gigabytes of data; and,

e) It is a very powerful yet uncomplicated software that can handle most corporate database applications.

If you are one of the ardent supporters of open source software worldwide, you would consider “what is MySQL” for Linux, which is 100% free and that the combination of Linux, Apache, My SQL and PHP (LAMP) is incomparable.

There are other things you know more about MySQL for beginners as a RDBMS for beginners; however, suffice to say that, a good sign up and online tutorial will help you more regarding its actual use.

I am a professional SEO writer. Most of the articles that I publish have something to do with my work, hobby and studies. You can also suggest to me articles that I should write and post. To see some of my articles, please click this link: http://www.happy-niches.blogspot.com. Thanks.

Tags: , ,

A Perfect Guide for MYSQL and Security – Creating the Best Linux Hosting Environment
By Kanquona Bhattacharjee

There are certain things you must be very careful of when you build your website. Performance of the website is a major factor in its success. This guide will give you a technical overview of your server performance. In this guide I am only concentrating on Linux environment as

1. It is cheaper.

2. It supports the fastest script engine PHP.

3. It has the default MySQL database.

4. Its web server is apache, which can be configured with less effort and

5. The Linux environment is most popular.

Before I begin, I must explain what makes a website performs rock solid. As you are aware in most of the websites database is a key factor which mainly stores and fetches data. A portal with slow database execution takes long time to execute and that can take away most of the visitors. If your portal accepts payment then security is the next thing, you shall be concerned of. So, my article will mainly revolve with these two features and a bit of other key areas which you would like to give focus on.

Hardware: This is the factor you must take seriously depending upon the kind of business you are doing and the number of visitors you are drawing. The hardware peripherals which affect the performance are mainly RAM and your CPU speed. RAM is a space which stores data temporarily for CPU access. But if your CPU cannot process faster, then transferring more data into the RAM area will not solve the problem, since the data will remain unprocessed. A better system is a balanced system and increasing just one parameter will never go to improve speed.

MySQL: Database is the most important parameter in terms of server performance. If I classify what makes a slow database or rather what you can do to improve data seek speed, then we get

1. Database design

2. Query standard and

3. Configuration as the primarily responsible reasons.

Database design: MySQL allows you to define data type definition in wide varieties. And you must use them optimally. Declaring the data type of a field as int(11) will unnecessarily eat up space, if the field stores value between 0 and 10. Remember, a good bridge is one which is has better architecture. Mere using good materials never build a good bridge. Here are a few guidelines.

A. For numeric value, guess what maximum value it might store and then select between tiny int, small int, medium int and int. Fields which you know will never store negative numbers should be unsigned.

B. For alphanumeric characters it is wise to select variable length than fixed length as variable length can adjust memory space depending upon size of data stored.

C. Normalize the table as much as possible. This decreases the chance of data redundancy. Also as tables get broken up, memory space usage decreases.

D. Proper indexing is the key for better search result. Index your table wisely and pragmatically. If you think, your table will be searched more on employee name than on employee code, create index based on employee name. Mysql arranges data based on available indexes, so search based on index results faster performance.

Query Standard: The fast data retrieval depends on the query you write. Here are a few tips on that.

A. Join query is always better than multiple nested queries. So try to use join queries if more than one table are involved in search.

B. Avoid Select * statement.

C. Try to test queries by writing them in alternate ways.

Server configuration is the last thing we can do to better the performance. Here are the important settings which affect the performance.

A. Key_buffer_size: This is the most important settings. The larger you set its value, the more MYISAM indexes store in memory. As most queries use indexes as search factor, memory plays better role than disk. The ideal settings is 25% of server memory but less than 50%, for improved performance.

B. The table cache: When mysql access a table, it places it in the cache. So increasing cache size plays an important role. So if you are accessing three tables, mysql opens three tables in the cache. Its default value is 64, but you must set it accordingly depending upon performance. An optimized one, not too high, not too low.

C. Sort_buffer: If you are sorting huge table then you must set its value high.

D. Read_rnd_buffer_size: The read_rnd_buffer_size is used after sort when rows are read in sorted order. If you are using many queries which use order by clause, increasing this value will help improve performance.

E. Query cache: first you must on query cache by setting query-cache-type=1 and query-cache-size can ideally be around 15-20 MB.

F. Tmp_table_size: This variable set the maximum size for a temporary table in memory. You must try avoiding temporary table by optimizing query. If the table becomes two large, MySQL creates a MyISAM table instead. Upping this value helps in speed, if temporary table size gets increased.

I would not discuss much on security issues rather would try to discuss it separately. But as an introduction SQL injection, Cross Side Scripting and HTTP trace are the most important security threat one website can get. SQL injection is a threat which exposes table name and data when a purposely built query is passed to the database through any web form. Basically when user input is incorrectly filtered for string literal escape characters. If we consider the below SQL statement

“Select * from employees where emp_name’=” + EmployeeName + “‘”

Now if the user input EmployeeName has any ‘ inside it, mysql cannot escape the string literal and exposes error.

Similarly if purportedly written javascript is passed through user input, Cross Side Scripting or XSS attack happens. This client side script can take charge of the website through hole exposed by the weekly programmed website code. I would like to discuss more with security issues. Till then happy reading!!

Kanquona Bhattacharjee is a freelance writer and blogger. She has completed her post graduation in English from the University of Calcutta. She has some deep insight into social problems and often present these infront of the most strongest medium. Her personal blog is http://journeytolearning.blogspot.com/ She can be contacted at kanquona@gmail.com

Tags: , , , , , ,

Create an MS Access Database – 9 Simple Steps to a Productive Database
By Joe D Richardson

STEP 1: Define the Purpose

Before you try to create an MS Access database first decide what the purpose for the database is. Software is much like a vehicle. Cars transport people from one place to another. Trucks can carry a few people and lots of cargo. The database you create should have a mission and purpose. It may be to track jobs, log activity, record and monitor sales or any of thousands of purposes.

STEP 2: Define Your Workflow

Most people skip this step before trying to create an MS Access database. You’ll regret it if you do! You have work to get done and already have steps you are probably following now to accomplish the job. You’re not yet using an access database to accomplish these tasks. Write these processes down in a step by step fashion. This is your workflow. Workflow usually starts with the arrival of some type of information. That information will need to be entered into the database once it’s built. What are you currently doing with that information? Using spreadsheets? Using paper? What steps are you taking once you receive that information? Do you process it? Does it go to someone else for approvals? Write down the workflow you are currently using to process that information from beginning to end. This is essential before creating your Access database.

STEP 3: Identify Forms & Reports

Next, take that workflow and review it step by step. Mark beside each step where you will need to enter information or retrieve information. Really that’s all you can do with a computer – put information in or get information out. Write down, beside each step, on your workflow the points where you need:

  • To enter data (form)
  • To modify data (form)
  • To View data on the screen (form)
  • To print data on a report (report)

STEP 4: Design Forms & Reports

After marking up your workflow with forms and reports you should next should draw out, on paper, what you want each form and each report to look like. This will become your database design. I suggest you use paper first. This is where you get creative and using paper is quick and simple. On each form and report you’ll want to write the names of the fields you want to use or see. Draw the buttons you will need too. Each form or report needs a title also. This will help you think and discover exactly what fields you need and where they need to be placed. Then, look over your reports and make sure you know where the data came from for that field or column.

STEP 5: Design Your Tables

Now you can design your tables. Based on each of your forms you can see what tables you will need. Normally each form will have a table, and that table will contain the fields from your form. Some of those fields may come from other tables but the bulk will normally come from one table.

  1. List the tables you will need and the fields for each.
  2. Each table should have a unique identifier “Primary Key” for each record. For example; if the table is full of drivers then a “Driver ID” key would be assigned to each driver record in the table. No two primary key fields in the database should be named the same.

STEP 6: Discover Your Relationships

Relationships in Access are not designed, they are discovered based on your actual business practices. Look for one-to-many or parent-child relationships. And place a foreign key in each of the child tables. For example, if you had a State table and a County table you know each state has many counties. This is a parent-child relationship. The State table should have a Key field ID. Place this same field ID in your County table. Make sure it’s the same field type, but is not a key field. The County table should have its own Key field.

STEP 7: Build Your Tables

After designing your tables you can build your tables. Microsoft included lots of tools to help you build the components for your database. I will not get into all the details in this article. It’s most important though, that you build your tables before you build your forms.

  1. Create your tables
  2. Create your fields
  3. Set your field properties
  4. Establish your relationships

STEP 8: Build Your Forms

After building your tables then you build your forms. Follow the paper design so you know where your fields should be placed. You can use wizards or add them by hand. Make sure you build your tables first before you build your forms.

STEP 9: Build your reports

After building your forms enter some test data so you can see what you are doing on your reports. Building reports is usually the last step.

Conclusion

As you can see the process goes in reverse when you build your database. If you don’t design from the top down and build from the bottom up then you will run into the problems 95% of people do when they try to create an access database.

THE RIGHT WAY TO CREATE AN MS ACCESS DATABASE

  1. Design Forms and Reports on paper
  2. Design Tables on paper
  3. Build Tables
  4. Build forms and reports

THE WRONG WAY TO CREATE AN MS ACCESS DATABASE

  1. Start building forms on the fly
  2. Try to build the tables to work with the forms
  3. Change the forms to fit the tables
  4. Build the reports
  5. Change the tables to fit the reports
  6. Change the forms to fit the tables
  7. Keep repeating the above steps until you are completely confused and frustrated!

Joe Richardson is a professional database developer and has been building MS Access databases since 1996. He has more than 300 clients in 4 countries across varied industries.

Visit Joe’s website to learn how you can create an Access Database or ask him to build yours.

Copyright © 1999 – 2011 Productive Data Solutions. All Rights Reserved.

Tags: ,
Back to top