php + mysql optimization

24
PHP + MySQL Optimization

Upload: klabcyscorpions-techblog

Post on 22-Nov-2014

944 views

Category:

Education


3 download

DESCRIPTION

 

TRANSCRIPT

Page 1: Php + MySql Optimization

PHP  +  MySQL  Optimization  

Page 2: Php + MySql Optimization

Introduction •  The report includes project-related (EU) queries, tables

or databases example. •  We can learn simple php/mysql optimization. •  Know the main idea of using index in a table.

Page 3: Php + MySql Optimization

Let’s start from simple cases

Page 4: Php + MySql Optimization

Let’s start from simple cases

Avoid excess variables function  isSomething()  {                  $is_something  =  false;                    if  ($some_condition_happens)  {                            $is_something  =  true;                    }                      return  $is_something;  }  

Page 5: Php + MySql Optimization

Let’s start from simple cases

Use cache (eg. APC, memcache)

Page 6: Php + MySql Optimization

Let’s start from simple cases

Avoid queries inside a loop.

$player_ids = array(1, 2, 3, 4, 5, 6, 7, 8); foreach ($player_ids as $id) { $players_infos[] = $db->query(“SELECT * FROM player WHERE id = ?”, array($id)); }

Page 7: Php + MySql Optimization

Let’s start from simple cases

Select only needed columns.

Page 8: Php + MySql Optimization

Let’s start from simple cases

count(*) vs count(col_name)

Page 9: Php + MySql Optimization

Let’s start from simple cases

limit 1 when retrieving a

unique row.

Page 10: Php + MySql Optimization

Index to remember

Page 11: Php + MySql Optimization

Index to remember

Without an index, MySQL must begin

with the first row and then read through the entire table to find the relevant rows.

Page 12: Php + MySql Optimization

Index to remember

If there is a choice between multiple

indexes, MySQL normally uses the index that finds the smallest number of rows

Page 13: Php + MySql Optimization

Index to remember

To retrieve rows from other tables when performing joins, MySQL can use indexes on columns more efficiently if they are declared as the same type and size.

(Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion.)

Page 14: Php + MySql Optimization

Index to remember

“Index has significant cost. So you should not create index everywhere.”

- http://lab.klab.org/

mysql-bootcamp/key.html

Page 15: Php + MySql Optimization

Examples

Page 16: Php + MySql Optimization

Examples Scenario : Imagine you have a table that will

store millions of records (eg. item_history) and you don’t have indexes.

Page 17: Php + MySql Optimization

Examples The table structure :

id int(10) unsigned

player_id int(10) unsigned

item_master_id int(10) unsigned

type enum('INC','DEC')

reason varchar(255)

num smallint(5) unsigned

purchase_history_id int(10) unsigned created

timestamp

Page 18: Php + MySql Optimization

Examples

The problem : When you try to select data from a table without index

See how many rows were checked after the search?

Page 19: Php + MySql Optimization

Examples

The solution : We are going to add index for the player_id column

ALTER TABLE `item_history` ADD INDEX ( `player_id` )

See the advantage of adding an index? The search took 1 row search only.

Page 20: Php + MySql Optimization

Examples

Upon looking on the table structure which column/s can be used as index when you want to retrieved a player’s item history?

Page 21: Php + MySql Optimization

Exercise Table Name : unit_history

Description : Tracks player’s record when he/she gained, evolved, etc.. a unit.

Table structure :

id int(10) player_id int(10) unit_master_id int(10) type enum(‘INC’, ‘DEC’, ‘EVOLVED’, ‘EXP’,

‘ITEM’) created timestamp

Page 22: Php + MySql Optimization

Exercise

The Scenario : List player ids who gained and evolved the unit

id 10

The Question : What are the possible index we might need in

order to retrieve the data faster.

Page 23: Php + MySql Optimization

Using index in joins

•  columns that you will use as the join condition ( ON a.id = b.id ) should be indexed.

•  LEFT JOIN will use indexed column from left ( a.id )

•  RIGHT JOIN will use indexed column from right ( b.id )

Page 24: Php + MySql Optimization

Did you know?

That we MySQL has a built-in profiling tool?

SET PROFILING = 1; SELECT COUNT(*) FROM table_name WHERE id = 1; SHOW PROFILE FOR QUERY 1;

SHOW PROFILES;