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.

42 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

Teo T 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