The database modeling is one of the most important Works when developing a web application. This is because the way the tables are set up is what dictates the application speed and one of the points that most impact is the number of columns and records.

Often, it is necessary to create numerous columns (fields) due to different situations. A customer base, for example, can contain numerous fields, but it is important to consider whether all these fields could not be changed by what we call indexes.

A special type of client may be stored in a separate table and being referenced from a code. If so, then we would have the table “customer” and a second, “type_customer”. This, in turn, would have a relation “one to many” from the type of client code (cod_typeCustomer). Then, an index could be applied in the field referring to the client’s type code as well as the customer itself.

The index of the tables is created from a very simple command. For new tables, the following syntax is used:

CREATE TABLE [Table Name](

Code  INT,

Name    VARCHAR(50),

INDEX (Code)


To add a new index on existing table we do it as follows::

CREATE INDEX [index name] ON [Table name]([Index field]);

By default, you must enter the index name. The expression “idx” before the name is the most common.

About MySQL, tables in dynamic format and lines with several columns can lead to a loss of performance in the application over time. Therefore, it is important to work with stress testing before actually going to the patterned database development environment. Thus it is avoided that the application is built upon a database that can often not be prepared to set optimally the number of records to be stored and retrieved later.

Check out more content on our blog!
Learn all about Scriptcase.

By ,

May 12, 2015


You might also like…

“We developed RISK4ALL, a SaaS platform that allows implementing GRC in an organization with total security and privacy.” (RISK4ALL)

Come and discover the success case of the Risk4all, software, a multilingual and multi-company SaaS...

Security in web applications

In this article, you will learn about web application security and the options within Scriptcase th...

“We have developed the Connected Citizen Card, which assists public administrators in decision-making and policy creation.”(Assist Soluções em TI)

Check below how Assist Soluções em TI used Scriptcase to modernize and streamline the development...

You might also like…

Get new posts, resources, offers and more each week.

We will use the information you provide to update you about our Newsletter and Special Offers. You can unsubscribe any time you want by clinck in a link in the footer of any email you receive from us, or by contacting us at Learn more about our Privacy Police.