Author Archives: needmoretoday

Mysql high traffic with join table

Posted by on April 17, 2013 No comments

Recently I encounter a trouble shooting. This is a high traffic system which record each of member all the hits and impression. And base on those records, system will decide next page site display order.
We got a lot of complain that it took too long time loading up next page and even more blank pages. In system backend, there are a bunch of lost db connection error. After dig deep, found out the script query using join. Most of developer like using join or sorting to do the query, yes, even me. It is clear and easy written down in script. But I did test, join and sorting require pull out select result into tmp, then msyql base on the tmp finish whole action. Although we could say tmp only using virtual or memory, will wipe off in very short time. But in high traffic, it will explode memory.
Well,if we only do select in query, and using array handle all the join and sorting. seems this way much faster and with no error.
So I brought up another question. Why php array handle much faster than mysql query handle.

Get large tables information

Posted by on February 1, 2012 No comments
1
2
3
4
5
6
7
8
9
SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

Freebsd setlocale

Posted by on January 5, 2012 No comments

Under FreeBSD, locale definitions are stored in the /usr/share/locale/ directory.

1
2
setlocale(LC_TIME,'zh_CN.UTF-8');
echo strftime('%A %d %B %Y', mktime()) . "<br />";

Switch back to default

1
setlocale(LC_ALL,NULL);

Pages

Posted by on December 10, 2011 No comments
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
abstract class page{
    public $head;
    public $content;
    public $foot;

    function __construct($head, $content, $foot){
       $this->head = $head;
       $this->content = $content;
       $this->foot = $foot;
    }
   
    public function getPage(){
       $smarty = new Smarty();
    }  
}

class login extends page{
   public function __construct($data){
      parent::__construct("login head", $data, "login foot");
   }
}
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
class talker{

    private $data = 'Hi';

    public function & get(){
        return $this->data;
    }
   
    public function out(){
        echo $this->data;
    }    

}

$aa = new talker();
$d = &$aa->get();

$aa->out();
$d = 'How';
$aa->out();
$d = 'Are';
$aa->out();
$d = 'You';
$aa->out();
?>

Create levels downline list

Posted by on October 26, 2011 No comments

Create table with referrer id, if using down line will encounter multiple to one problem.  So I am using referrer id point to member upline, 1 as basic system id. One member only have one reference member,  one member can referrer more than one member.

Table Member:

Member_id Referrer_id
25 1
123 25
136 25
150 123
154 25

Here is one example how I grab member_id as 25 ‘s all downline.

1
$q = "SELECT member_id FROM members WHERE referrer_id = 25;"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<!--?php
   function getTotal($products_costs, $tax) {
       $total = 0.00;
       $callback =
             function ($pricePerItem) use ($tax, &amp;$total)
             {
                  $total += $pricePerItem * ($tax + 1.0);
             };
       array_walk($products_costs, $callback);
       return round($total, 2);
}
 -------------------------------------------------------------------------
function slashit(&$aray, $db_link) {
     foreach ($aray as $key => &$value){
         if(is_array($value)) slashit($value, $link);
         else $aray[$key] = mysql_real_escape_string($value, $db_link);
     }
}
// Test: $fruits = array (
"fruits"  => array("a" => "or'ange", "b" => "ban'ana", "c" => "apple'"),
 "numbers" => array(1, 2, 3, 4, 5, 6),
 "holes"   => array("fir'st", 5 => "sec'ond", "thir'd"),
 "odma"    => "jugo'slavija"
);
?-->

Create mysql all datases tables fields head cvs file

Posted by on October 25, 2011 No comments

mysqldump –skip-lock-tables -u root -t -T ‘/your files saved path’ information_schema COLUMNS –fields-enclosed-by=\”  –fields-terminated-by=,