Monday, December 19, 2011

Documentation on handling larger database



Hi Techies,
       I created a documentation for handling larger database. Make use of it & share any other tips which I missed out.

Database server: Buy a server that can handle larger data's

Database engine: We can use either MyISAM or INNODB.

Stick to your basics right:
1)     Create table with proper indexing & primary key

2)     Don't use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()

3)     Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

4)     Use index to filter rows while searching

5)     Don’t use * in select when you’re not going to retrieve 60% of the columns. Make it specific like (userid, username, doj, lastlogin etc)

6)     Don't Index Everything

7)     A NULL data type can take more room to store than NOT NULL

8)     Use mysql_pconnect instead of mysql_connect in PHP

9)     Don’t use parenthesis in the select query instead use the table name as prefix for the column name.

10)  In the pagination don’t use offset, Just use Limit N. It makes pagination faster.
E.g.: With offset Select * from pages limit 10,20
Without offset Select * from pages where id > 10 limit 20

11)  Use INET_ATON and INET_NTOA for IP addresses

Optimization Tips:
1)     Use OPTIMIZE for each table
a.      E.g. OPTIMIZE Table tablename
                                                              i.      This will work only in MYISAM Engine.

                                                            ii.      While optimizing the entire table will be locked, so it should be done only when the site is offline & maintenance period

2)     To test the server speed, Just execute the following query in mysql
a.      SELECT BENCHMARK(1000000,10+10);

b.      The above command executes the expression 10 + 10 one million times. This will return 0 as result, but the execution time should be noted.
This command as to run different time of the day to check how the server handles the request.

3)     Create index for the table, we should make proper indexing to the fields which we’re going to search for the results.

4)     Before making a select query check it with Explain function & check whether the query is optimized to its best. This will be very useful when we use for complex queries.

5)     Use stored procedures to avoid bandwidth wastage

6)     Partition your database/table

7)     Delete data that you don’t need. Tables will grow, so monitor table sizes regularly.

Secure the Data’s:
While inserting or fetching data’s from the website, we need to be careful. We should follow the steps mentioned below
1)     Use mysql_real_escape_string() for the datas that has user interaction like Inserting fields & query strings

2)     Use POST method as much as possible

3)     Filter each inputs to make it secure. Use strip_tags().

4)     When you're getting the values from query string, use (int) function for getting integer values & if you want to get a string or characters use substring to get the values to a certain limit (For example: An hacker can pass ascii code to the query string & can access the database. So, we can limit upto 10 characters in the query string. If we do so, sql injection will be prevented).

5)     If you're using AJAX, pass the values by post method.

6)     Don't allow php files & other executable files in the file upload option. If you do so, the hackers can write their script in php file & upload it to your server & can access any data’s that they want.

7)     In production path don't print the mysql error code or sql query. If any error displayed then the hackers can find out the table structure for the website & can use it accordingly.

95 comments:

Lia said...

Hi this is nice blog and there's a lot of useful information that can help us on what to do in larger database. Thanks.

PHP Developers

Anonymous said...

Very interesting post, and great blog! I work for a new social blogging site called glipho.com, and was just wondering if you would be interested in sharing your posts there with us? It wouldn't affect your blog here in any way, and I know there are lots of aspiring programmers and developers who would love to read through your work. Let me know what you think!

All the best,

Teo

optisol biz said...

Your topic was great! Thanks for taking a moment to draft such an interesting piece… Application Development using Angularjs

Manjot kaur said...


Great information provided. I appreciate your work. I like the way you write. Awesome, keep it up.

6 Month php Industrial Training in Chandigarh
6 weeks php industrial training in chandigarh

jasmin jew said...

Great info.. thanks for sharing .

PHP training in Lucknow

Web Development Service in India said...

Thank you so much for sharing. This is such a raw and honest post. This is really very good post for handling larger database.

Naviya Nair said...

Great Article..
PHP Training in Chennai
Online PHP Training
Online PHP Training India
PHP Training Chennai
PHP Training institute in Chennai

Anonymous said...

Thank you for sharing this information.I have gone through your blog and its very wonderful PHP training in Chennai

Dinesh Kumar said...

Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about PHP.
http://thecreatingexperts.com/php-training-in-chennai/

Dinesh Kumar said...

Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about PHP.
http://thecreatingexperts.com/php-training-in-chennai/

SAP Success Factor Training in Chennai Chennai said...

Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about php

Pragalya Suresh said...

Informative content on php

Priya Tamil said...


Another great blog information. Great tips, and awesome way to get exert tips from everyone.i like that kind of interesting information.
php training

SiliconIT Hub said...

PHP Development is one of the cost friendly and effective open source platforms introduced till date. At Silicon IT Hub, we offer advance PHP development solutions for your powerful website application development. We prove as a cost friendly and qualitative web application development company for your business/organization. We have hired experienced and highly professional web developers that have creative flair and expertise over LAMP and WAMP architecture.

lolagarto said...

Great post I was checking continuously this blog and I’m impressed! Very helpful info specially the last part :) I handle such info much. I was looking for this particular information for a long time. Thanks and good luck:


Packers And Movers BangalorePackers And Movers DoddakallasandraPackers And Movers gaviopuramPackers And Movers ISROPackers And Movers jalahalliPackers And Movers Electronics City bengaluruPackers And Movers Chikkalasandra bengaluru

Anonymous said...

Very informative, keep posting such good articles, it really helps to know about things. php training in jalandhar

prethikarajesh said...

Wonderful article, very useful and well explanation. Your post is extremely incredible. I will refer this to my candidates...
seo company in chennai

sindhu said...

Good post. I learn something totally new and challenging on blogs I stumble upon on a daily basis. It will always be interesting to read articles from other authors and practice something from their websites...

CRO Agency in Chennai

Sowmiya said...

your postings are really awesome and it is very much nice and interesting thus it is very much reliable and it is nice too. thanks for sharing these information it is really helpful and useful too.




Best Informatica Training institute in chennai

Bhavya Kumar said...

My friend Suggest me this blog and I can say this is the best blog to get the basic knowledge.Thank you so much for this Selenium Training in Chennai

Unknown said...
This comment has been removed by the author.
David Stephan said...

Great suggestions. Your blog is very helpful and your way of teaching is awesome. As a Asp.net developer, I have to face various issues in my daily work and your blog is the right place to solve my problems. Please share your strategies on daily basis to get better solutions of problems.

pnp universe said...

10 very best Programming Languages that you wish to research In 2017
Read Artical @ https://goo.gl/7NXLxn
Website design and Developmet service in NY

Stepherd said...

wow really nice. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.

Recruitment Consultancy in Bangalore

Nandhini said...

This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
Pest Control in Chennai

Aasha said...

Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing.
Digital Marketing Company in Chennai

Aasha said...

Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing.
Digital Marketing Company in Chennai

Sathya G said...

This post is much helpful for us. This is really very massive value to all the readers and it
will be the only reason for the post to get popular with great authority.
IOS Training in Chennai

Thamarai Ravi said...

Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information. I would like to suggest your blog in my dude circle.
SEO Company in Chennai

Anu said...

I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

iOS Training in Chennai
Android Training in Chennai
php Training in Chennai

amala amala said...

Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information. I would like to suggest your blog in my dude circle.
Jobs in Chennai
Jobs in Bangalore
Jobs in Delhi
Jobs in Hyderabad
Jobs in Kolkata
Jobs in Mumbai
Jobs in Noida
Jobs in Pune

jeslin said...

Helpful as always. Every post you write produce a massive value to your readers that is the only reason it is so popular and has great authority.


House Cleaning Services Mumbai

Vamshi Krishna said...

Nice to read your article! very informative.So, please keep posting PHP Stuff here Thanks man.......

Shalini said...

Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

Digital Marketing Company in Chennai

Lucky Srivastava said...

Hey Very nice Blog,Thanks For Sharing..

Best Php industrial training in Lucknow
best summer training in lucknow
Embedded system training in lucknow
Best Industrial training company in lucknow
best Php training center

Vamshi Krishna said...

Nice to read your article! very informative.So, please keep posting PHP Stuff here Thanks.......

Buy Contact Lenses said...

Amazing blog and very interesting stuff you got here! I definitely learned a lot from reading through some of your earlier posts as well and decided to drop a comment on this one!

Lucky Srivastava said...

Hey Very nice Blog,Thanks For Sharing..

Best python training in lucknow
Python training in Lucknow
Softpro Learning Center

Lucky Srivastava said...

Hey Gyss Check out this...

Softpro Learning Center (SLC)is the training wing of Softpro India Computer Technologies Pvt.
Limited. SLC established itself in the year 2008.
SLC offer an intensive and extensive range of training/internship programs for B.Tech, BCA, MCA & Diploma students.
Softpro Learning Center is a best institute in Lucknow extends in depth knowledge of technology like .Net, Java, PHP and Android and also an opportunity to practically apply their fundamentals. SLC’s objective is to provide skilled manpower to support the vast development programs.

Lucky Srivastava said...

Hey Gyss Check out this...

Softpro Learning Center (SLC)is the training wing of Softpro India Computer Technologies Pvt.
Limited. SLC established itself in the year 2008.
SLC offer an intensive and extensive range of training/internship programs for B.Tech, BCA, MCA & Diploma students.
Softpro Learning Center is a best institute in Lucknow extends in depth knowledge of technology like .Net, Java, PHP and Android and also an opportunity to practically apply their fundamentals. SLC’s objective is to provide skilled manpower to support the vast development programs.


Lucky Srivastava said...

Hey Gyss Check out this...

Softpro Learning Center (SLC)is the training wing of Softpro India Computer Technologies Pvt.
Limited. SLC established itself in the year 2008.
SLC offer an intensive and extensive range of training/internship programs for B.Tech, BCA, MCA & Diploma students.
Softpro Learning Center is a best institute in Lucknow extends in depth knowledge of technology like .Net, Java, PHP and Android and also an opportunity to practically apply their fundamentals. SLC’s objective is to provide skilled manpower to support the vast development programs.

Online Training in Hyderabad said...

very useful info, and please keep updating........
Best Online Software Training

Philips Huges said...

Its very useful to me. Wonderful blog.. Thanks for sharing informative Post.

Installment loans
Payday loans
Title loans

Unknown said...


I have seen lot blogs and Information on othersites But in this PHP Blog Information is very useful thanks for sharing it........

sandhosh said...

Really Good blog post.provided a helpful information.I hope that you will post more updates like this.
Digital marketing company in Chennai

Anusuya said...

Someone essentially lend a hand to make severely posts I would state. That is the very first time I frequented your website page and thus far? I surprised with the analysis you made to create this particular submit incredible. Fantastic job!

Home Interior Decorators in Chennai
Home Interior Designers in Chennai
Home Interiors in Chennai

Aashi said...

I must thank you for the efforts you have put in penning this site. I am hoping to check out the same high-grade content by you later on as well. In truth, your creative writing abilities has inspired me to get my own, personal blog now..
Digital Marketing Training in Chennai
MSBI Training in Chennai
VMWare Training in Chennai

Bharathi said...

Thank you so much for posting like this. I enjoyed much more here. So please keep update like this.

Medical Insurance Dubai

Health Insurance Dubai

Insurance Companies in Dubai

Karthi Keyan said...

Interesting blog post.This blog shows that you have a great future as a content writer.waiting for more updates...

Digital Marketing Company in India

Digital Marketing Services in India

Digital Marketing Agency in India

Digital Marketing Companies in India

Digital Marketing Companies India

Digital Marketing Agency India

subuvenni said...

i really like this blog.And i got more information's from this blog.thanks for sharing!!!!Digital Marketing Company in Chennai

lakshmi deepa said...

Thank you for your sharing information..waiting for more information.. very happy to visit your post..
SAP HR Training in Chennai
SAP ABAP Training in Chennai
SAP FICO Training in Chennai

avantikajoy said...

There are many interesting information included and i can easily understand all given information.I post something on my blog to post something, or wait to post something worth saying. Keep update more information....
Vitiligo Treatment
Dry Skin Treatment

Avantikajoy said...

Someone essentially lend a hand to make severely posts I would state. That is the very first time I frequented your website page and thus far? I surprised with the analysis you made to create this particular submit incredible. Fantastic job!
Vitiligo Treatment
Psoriasis Treatment
Dry Skin Treatment

dazzling said...


really it was an more interesting blog to read keep on updating you are focused on your topic without any deviation.really it looks good to read and inspiring to all those who has visited your blog.
DOTNET TRAINING IN CHENNAI
JAVA TRAINING IN CHENNAI

venu puram said...

Nice and Good Documentation on handling larger database well you have provided the perfect set of things to be done and your documentation is also very useful with effective of proper knowledge the easy way of handling the documentation is well written When I was having my PMP Course in Kuwait I used to create Documentations about PMP and what all will be in PMP etc Well thankyou so much for the blog.

kokila zenrays said...

Really good useful information about PHP. Keep sharing. PHP Training Bangalore

Raghu said...

Just read your website. Good one. I liked it. Keep going. you are a best writer your site is very useful and informative thanks for sharing!
Herbal Shampoo
Psoriasis Oil

Nishabablu said...

i really like this blog.And i got more information's from this blog.thanks for sharing!!!!
Best Interior Designers in Chennai
Architects in Chennai
Home Interiors in Chennai

Sumathi said...

I appreciate your style of writing because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also informative. I hope to read more and more interesting articles from your blog. All the best.
Psoriasis Treatment

devi kala said...

This is a great article, I have been always to read something with specific tips! I will have to work on the time for scheduling my learning.
Peridotsystems
Hadoop Training in Chennai

kokila zenrays said...

Thanks for sharing this post about PHP. PHP is really good information about learning. PHP training in Bangalore

ananthi said...

this is very nice post and thanks for sharing this wonderful information to us.i really would like to appreciate to your attention for us.this course very useful to us this gives the big opportunity to us i like this information very much.

Hadoop Training in Chennai

Sita said...

Interesting blog post. This blog shows that you have a great future as a content writer. waiting for more updates.
Herbal Shampoo
Dandruff Treatment
Antifungal Cream
Vitiligo Medicines

Anonymous said...

The articles you are writing is relevant to all types of users and makes the reader to read the information with interest.I recommended you to write articles various topics so that the user could get the general knowledge about what is happening around the world. I hope you would definitely do it.

Iperidigi
Android app development company in chennai

Nazeer Rahaman said...

Hi, I worked on PHP, AJAX, JAVASCRIPT set of projects where I was handled n number of Projects Which can be given the preference equal to all the projects As I completed my PMP course in bangalore I was supposed to make the perfect set of projects in all these domains ineffective to other projects Thank you for providing the information Please keep updating

Domnic said...

Anybody want to know How to trace the mobile with location and get more details for tracking.Read this blog...

Mobile Number Tracker | Phone Number Tracker With Location | Mobile Location Tracker | How To Track A Phone Number | Phone Number Tracker | Phone Number Search

Abitha Chetna said...

These ways are very simple and very much useful, as a beginner level these helped me a lot thanks fore sharing these kinds of useful and knowledgeable information.

Selenium Training in Chennai

Riya karakoti said...

Best Hadoop Training Institutes in Noida - Training is what makes a person able to doing something that cannot be accomplished without right education and information. Training may be very critical for those activities that may be carried out only with the aid of informed people.


Best Hadoop Training Institutes In Noida

best sas training institutes in noida

best sap training institutes in noida

best linux training institutes in noida

Gopi Vj said...

i have really enjoyed sharing your website. thank you so much for your sharing this document. this document more useful and improve our knowledge.
hadoop training in chennai

Nishanth Kannan said...

Thank you for sharing such a nice and interesting blog..

SAP HR Training in Chennai

Guru Guru said...

Thanks for making me to spent my 5 minutes us best.......
SAP MM Training in Chennai

sharath said...

I like it and help me to development very well.
Thank you for this brief explanation and very nice information.
Well, got a good knowledge.

Hadoop Training in Chennai

Tamil said...

Wonderful blog and very useful.Thanks for sharing this valuable information...
SAP scm Training in Chennai

buddy vino said...

I got lot of information from your blog. Keep on sharing like this blog...
SAP FICO Training in Chennai

Ancy merina said...

What a fantastic and informative blog! i enjoyed reading this, and I must say your blog is great! Keep up the excellent work. You have a magical talent of holding readers mind.
Best website development company in Bangalore
Best web design company in Bangalore
Ecommerce Website Design and Development Services in Bangalore
Outsource magento ecommerce services india

Rohi Shri said...


It is really very excellent,I find all articles was amazing

Online 3d printing
3D printing in Coimbatore
3D printing service in Coimbatore

Anonymous said...

Best Training institute in bangalore
java training institute in bangalore
digital marketing training in bangalore
python training in bangalore
aws training in bangalore
devops training institutes in bangalore

Orange Techno Mind said...

Nice blog thanks for share this post
DevOps Training in chennai
aws training in chennai
azure training in chennai
dot net training in chennai
infomatica training in chennai

Heera Roja said...

Informative blog , Nice work .. keep doing ..
Greens Technology Offers you a best Selenium Training with 100% Placement Institutes in Tambaram Chennai.
Selenium Training in Tambaram Chennai
Selenium Placement Training institutes in Chennai

Sophia sage said...

It’s really amazing that we can record what our visitors do on our site. Thanks for sharing this awesome guide. I’m happy that I came across with your site this article is on point,thanks again and have a great day. Keep update more information..
Software Testing Training in Chennai
SEO Training in Chennai
Informatica Training in Chennai
Digital Marketing Training in Chennai

jenisha said...

All given information was wonderful and thanks for sharing
Informatica Training in Chennai
SAS Training in Chennai

CIIT Noida said...

SAP Training Institute in Noida. We provide IT training based on corporate standards that help students to be prepared for industries. We are the Best SAP HANA training in Noida, is one of the best results oriented SAP HANA training institute in Noida, offering best practical, theoretical and experimental knowledge in SAP HANA Training in Noida. At SAP HANA training is conducted by 6 years of experience in managing real time projects.HANA, stands for "High-Performance Analytic Appliance" is a memory, column relational database management system developed and marketed by HANA's architecture is designed to handle both high transaction rates and complex query processing on the same platform.HANA is designed to replicate and ingest structured data from SAP and non SAP relational databases, applications, and other systems quickly.

SAP Training Institute in Noida
sap training in noida
sap institute in noida
sap course in noida
best sap training institute in noida

CIIT Noida said...

CIITN is the Best Php training institute in Noida and delhi Ncr. You will get Live Project Training on PHP by our PHP expert who have 5+ year industrial experience.Focus on practical and live project training. In our PHP training, we you will learn core PHP, advance PHP, HTML, CSS, JavaScript, jQuery, Bootstrap, Cake PHP and Wordpress.CIITN provides 100% job assistance in PHP training. CIITN is well known PHP coaching center because our 100% PHP students are placed now.


Ciitnoida provides Core and java training institute in noida. We have a team of experienced Java professionals who help our students learn Java with the help of Live Base Projects. The object-oriented, class-based build of Java has made it one of most popular programming languages and the demand of professionals with certification in Advance Java training is at an all-time high not just in India but foreign countries too.

By helping our students understand the fundamentals and Advance concepts of Java, we prepare them for a successful programming career. With over 13 years of sound experience, we have successfully trained hundreds of students in Noida and have been able to turn ourselves into an institute for best Java training in Noida.


java training institute in noida
php training in noida
linux training in noida
linux institute in noida
java course in noida

Kaashvi mohan said...

You made some decent factors there. I looked on the internet for the difficulty and found most individuals will associate with along with your website.Keep update more excellent posts.
Online 3d printing
3D printing in Coimbatore
3D printing service in Coimbatore
3D printing in Chennai

Kalai chellam said...

Its really an Excellent post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog. Thanks for sharing....

OFFERS IN CHENNAI

IICE Indore said...

I am really happy to say it’s an interesting post to read. I learn new information from your article, you are doing a great job.
php training in IndoreKeep Posting:)

CIIT Noida said...

CIITN is located in Prime location in Noida having best connectivity via all modes of public transport. CIITN offer both

weekend and weekdays courses to facilitate Hadoop aspirants. Among all Hadoop Training Institute in Noida , CIITN's Big Data and Hadoop Certification course is designed to prepare you to match

all required knowledge for real time job assignment in the Big Data world with top level companies. CIITN puts more focus in project based training

and facilitated with Hadoop 2.7 with Cloud Lab—a cloud-based Hadoop environment lab setup for hands-on experience.

CIITNOIDA is the good choice for Big Data Hadoop

Training in NOIDA
in the final year. I have also completed my summer training from here. It provides high quality Hadoop training with Live

projects. The best thing about CIITNOIDA is its experienced trainers and updated course content. They even provide you placement guidance and have

their own development cell. You can attend their free demo class and then decide.

Hadoop Training in Noida
Big Data Hadoop Training in Noida

Mohit Verma said...

thanks

Mohit Verma said...

This information you provided in the blog that is really unique. Thanks for taking this opportunity to discuss this, I feel strongly about this and I enjoy learning about this topic. I have found it enormously useful. I like the way of representation about the topic. Well explained. Keep sharing.
Thanks and Regards,
Mohit Verma
Seo Company in Lucknow |Seo Services in Lucknow

Rental Mobil Malang | Sewa Mobil Malang said...

Artikel Bagus, Terus menulis dan berkarya. sekarang jarang update yaa ?

Amelia said...

It is really a great and useful piece of info. I’m glad that you shared this helpful info with us. Please keep us informed like this. Thank you for sharing.

Best Architects in Chennai
Turnkey Interior Contractors in Chennai
Interior Contractors in Chennai
Architecture Firms in Chennai

Dekha Jayga said...

Very nice guide to me and for PHP beginners students ThesePHP Tutorials helpful to me.
Your blog is nice to get useful guide..

Sopiya lla said...

Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

Wellnesscentres in Chennai
Weightloss in Chennai
Weightgain in Chennai

cyber news said...

thanks for sharing the information.
Indian Cyber Army’s most awaited internship is live now. Summer Internship 2018 on “ Ethical hacking” and book your seats before it runs out.Candidates have to get themselves registered to be a part of this Internship program. Here internship will give you on-the-job experience, help you learn whether you and Cyber security industry are a good match and can provide you with valuable connections and references. Here interns are usually exposed to a wide variety of tasks and responsibilities which allows the intern to showcase their strengths by working on projects for various managers that work on different parts of Indian Cyber Army. Becoming a high performing intern is a fantastic way to improve your employment prospects. This internship can be a great way to get your foot in the door of success with a prestigious or desirable Organization.As career in ethical hacking is most in demand.

cyber news said...

Thanks for the information.It is really nice .Information security is the set of processes that maintain the confidentiality, integrity and availability of business data in its various forms.In this age of Technology advancement, computer and information technology have not only brought convenience to citizens in modern life but also for policemen & various Government officials of the nation to fight cybercrime through various modus operandi. Indian Cyber Army has been dedicated in fighting cyber crime, striving to maintain law and order in cyberspace so as to ensure that everyone remains digitally safe.Read more:- Information Security