CODEWITHSUNDEEP
phpmysql
Ragavendran Ramesh
Ragavendran Ramesh

Reputation: 358

How to optimize webpage and databases of a large content websites

A website which is related to shopping e-commerce. The website having huge number of data to be stored in DB. so far the products table in db is having nearly 18K records with 20 fields. i have created the db structure as follows

 - brands
 - products
 - shopslist
 - shopping_details

The website is having 100+ pages everything related to products and brands. 

eg.pages are 
www.example.com/nokia-mobile-phones.php , www.example.com/samsung-mobile-phones.php 
www.example.com/samsung-galaxy-s3-price.php,www.example.com/android-mobile-phones.php

i have written the queries to fetch the product details corresponding to the pages dynamically. This makes my website some what slower in grabbing the data from db ans displaying.

Currently website is having nearly 14k to 15k visits per day. suddenly the website becomes very slow and stops responding for more than 10hrs a day. The technical people on hosting side said the problem is due to more number of requests in a single day and your db occupies 75% of the memory , optimize your site and db for better results . The website is hosted in VPS with better configurations.

Kindly give a solutions for optimizing a website , queries and db.

Upvotes: 0

Views: 2016

Answers (3)

Bud Damyanov
Bud Damyanov

Reputation: 31889

Profile your code to pinpoint bottlenecks

Hoare's dictum states that Premature optimization is the root of all evil, an important thing to keep in mind when trying to make your web sites faster. Before changing your code, you'll need to determine what is causing it to be slow. You may go through this guide, and many others on optimizing PHP, when the issue might instead be database-related or network-related. By profiling your PHP code, you can try to pinpoint bottlenecks.

Upgrade your version of PHP

The team of developers who maintain the PHP engine have made a number of significant performance improvements over the years. If your web server is still running an older version, such as PHP 3 or PHP 4, you may want to investigate upgrading before you try to optimize your code.

Use caching

Making use of a caching module, such as Memcache, or a templating system which supports caching, such as Smarty, can help to improve the performance of your website by caching database results and rendered pages.

Use output buffering

PHP uses a memory buffer to store all of the data that your script tries to print. This buffer can make your pages seem slow, because your users have to wait for the buffer to fill up before it sends them any data. Fortunately, you can make some changes that will force PHP to flush the output buffers sooner, and more often, making your site feel faster to your users.

Avoid writing naive setters and getters

When writing classes in PHP, you can save time and speed up your scripts by working with object properties directly, rather than writing naive setters and getters. In the following example, the dog class uses the setName() and getName() methods for accessing the name property.

class dog {
  public $name = '';

  public function setName($name) {
    $this->name = $name;
  }

  public function getName() {
    return $this->name;
  }
}

Notice that setName() and getName() do nothing more than store and return the name property, respectively.

$rover = new dog();
$rover->setName('rover');
echo $rover->getName();

Setting and calling the name property directly can run up to 100% faster, as well as cutting down on development time.

$rover = new dog();
$rover->name = 'rover';
echo $rover->name;

Don't copy variables for no reason

Sometimes PHP novices attempt to make their code "cleaner" by copying predefined variables to variables with shorter names before working with them. What this actually results in is doubled memory consumption (when the variable is altered), and therefore, slow scripts. In the following example, if a user had inserted 512KB worth of characters into a textarea field. This implementation would result in nearly 1MB of memory being used.

$description = strip_tags($_POST['description']);
echo $description;

There's no reason to copy the variable above. You can simply do this operation inline and avoid the extra memory consumption:

echo strip_tags($_POST['description']);

Avoid doing SQL queries within a loop

A common mistake is placing a SQL query inside of a loop. This results in multiple round trips to the database, and significantly slower scripts. In the example below, you can change the loop to build a single SQL query and insert all of your users at once.

foreach ($userList as $user) {
  $query = 'INSERT INTO users (first_name,last_name) VALUES("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
  mysql_query($query);
  }

Produces:

INSERT INTO users (first_name,last_name) VALUES("John", "Doe")

Instead of using a loop, you can combine the data into a single database query.

$userData = array();
foreach ($userList as $user) {
    $userData[] = '("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
 }
$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $userData);
mysql_query($query);

Produces:

INSERT INTO users (first_name,last_name) VALUES("John", "Doe"),("Jane", "Doe")...

A good approach when dealing with SQL optimization is to select ONLY fields you need, not all of them in your case. This almost always have a huge impact on performance, especially when fields are BLOB's. And, the indexing is also very important, BUT only if you have created it properly. A use of LIMIT clause in your query is also good idea, if you do not need to display your result at once.

Here is more info about optimizing JavaScript code and here for optimizing web graphics.

Upvotes: 2

Alireza
Alireza

Reputation: 6868

  • Fetch images,styles and so on from a static domain.
  • Use Master & Slave for your databases
  • check indexes,schema and data types in your tables.

I can highly assure you that your database settings,indexes, etc are suffering. If you give more information about your database tables then we would help you with nicer answers ;)

Upvotes: 1

Sathishkumar
Sathishkumar

Reputation: 3414

  1. Use Caching Systems

  2. Avoid Resizing Images in HTML, If an image is originally 1280x900px in dimension. (i.e. <img width="400" height="280" src="myimage.jpg" />)

  3. Use External JS and CSS Files

  4. Optimize the Way You Write Code - For example, instead of using <h1><em>Your heading</em></h1>, you can easily use CSS to make your headings italics using the font-style property

  5. Use a Content Delivery Network (CDN)

Upvotes: 2

Related Questions