SkiPHP -- Database Basics for PHP

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.1
Insert Picture Here
Database Basics
for PHP
Programmers
Dave Stokes
MySQL Community Manager
David.Stokes@Oracle.com
@stoker
Slideshare.net/davidmstokes
Insert Picture Here

Safe Harbor
The following is intended to outline our general product direction. It
is intended for information purposes only, and may not be
incorporated into any contract. It is not a commitment to deliver any
material, code, or functionality, and should not be relied upon in
making purchasing decision. The development, release, and timing
of any features or functionality described for Oracle’s products
remains at the sole discretion of Oracle.

MySQL
 Most popular database on the web
 Ubiquitous
 16+ million instances
 Feeds 80% of Hadoop installs
 20 Years Old

PHP
 Most popular language on the web
 Ubiquitous
 Millions instances
 20 Years Old

But what
have you
done for us
lately??

http://www.thecompletelistoffeatures.com/

PHP 7 http://php.net/manual/en/migration70.new-features.php
● Scalar type declarations
● Return type declarations
● Null coalesce operator
● Spaceship operator
● And many more

Relational Data
● Based on relational calculus, set theory
● Been heavily used for decades
● Many vendors
● Goal: Store data efficiently

PHP SQL
● 80%+ of website
● Rich, vibrant, & supportive
community
● Object Orientated/Procedural
● Still main data store
● 'Standards' based
● Declarative
➔ OO/Procedural & Declarative Languages do not mix
easily
➔Impedance mismatch
➔Www.cd.utexas.edu~/Drafts/2005/PLDBProblem.pdf

Don't Panic!Don't Panic!

Mechanical Basics
● Application makes connection to database
● User is authenticated
– Query sent to myqld server
● Permissions checked
● Query syntax checked
● Query plan produced/executed
● Results returned to application
● Connection torn down

Mechanical Basics
Application mysqld

Example
<?php
$db = new mysqli('host', 'user', 'password', 'demo');
if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}

Example continues
// Performing SQL query
$my_query=
”SELECT name, show_size FROM `users` WHERE `active` = 1”;
$if(!$result = $db->query($my_query)){
die('There was an error running the query [' . $db->error . ']');
}

Examples continued
// Free result set
$result→free;
// Closing connection
$db→close();
?>

PHP Syntax
● The Syntax for PHP working with MySQL is very well documented.
● Stackoverflow and Quora do not count as documentation!!
● Two APIs – both procedural or OO
– (Do not use old mysql API)
● PDO – General database neutral
● Mysqli – MySQL Specific
● Millions of lines of examples

So if it is so simple ...
Why are there so
many application with
bad queries?!?!?

Problem 1 – SQL Itself
● SQL - Structured Query Language
● Is not taught widely
● Is a descriptive language (NOT procedural or object orientated)
– Describe what you WANT not how to make it
● Built on set theory (Also not taught widely)
● You can not tell a bad query from a good one just by looking!!!!!

Problem 2 – Coders!!!
● Thinking of data as an object or a single line
● Not letting the database do the heavy work
● Lack of normalizing or architecting data
● De normalize at your own risk
● Schemaless at your own risk

Quick SQL
● Descriptive language
● Data Description Language
– Schema design, describes data
● INT, CHAR, BLOB, etc.
● Default vales, character sets, etc.
● Data Manipulation Language
– Use data
● SELECT, UPDATE, INSERT, DELETE

Example Query
SELECT ID, Name, Population
FROM City
WHERE Population > 1000000
ORDER BY Name

Example Query
FROM City
ORDER BY Name
Data Desired

Example Query
FROM City
ORDER BY Name
Table where data is stored

Example Query
FROM City
ORDER BY Name
Qualifiers

JOINs or connecting two tables
SELECT City.Name, Country.name, City.Population
FROM City
JOIN Country ON (Country.code=City.CountryCode)

JOINs or connecting two tables
FROM City
First or LEFT table
Key or Index common
to both tables

Please Google SQL Venn Diagram
and print one out please!!!

But is this a good query???????
● Is the following a good query?
FROM City

But is this a good query???????
● Is the following a good query?
FROM City
Can Not Tell from the
AVAILABLE INFORMATION!!!!

A More Realistic Query
SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS
customer,
address.phone, film.title
FROM rental INNER JOIN cust
INNER JOIN address ON customer.address_id = address.address_id
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL AND
rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
LIMIT 5;

Getting to Good
● Do you have right column names, right table names?
● Are the keys correct?
● Units correct? Was that prior Population in ones, millions?
● Can use use indexes to speed query?

What Does the Server Do With a Query?
● Does user have permissions to talk to server?
● Is query syntax correct?
● Does user have permissions for requested data?
● What is the most efficient way to get that data? (Query Plan)
● Execute
● Return data

Remember this?
FROM City

It generates a 63 line Optimizer Trace
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "5132.14"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 239,
"rows_produced_per_join": 239,
"filtered": "100.00",
"cost_info": {
"read_cost": "6.00",
"eval_cost": "47.80",
"prefix_cost": "53.80",
"data_read_per_join": "61K"
},
"used_columns": [
"Code",
"Name"
]
}
},
{
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"world.Country.Code"
],
"rows_examined_per_scan": 17,
"rows_produced_per_join": 4231,
"filtered": "100.00",
"cost_info": {
"read_cost": "4231.95",
"eval_cost": "846.39",
"prefix_cost": "5132.14",
"data_read_per_join": "727K"
},
"used_columns": [
"Name",
"CountryCode",
"Population"
]
}
}
]
}
}

EXPLAIN

Visual Explain

More Complex Query

Each Column In a SQL Statement ...
● Adds an additional factorial to the complexity of
the query plan
● So a SELECT with five columns has 120
combinations
● 5! = 5 x 4 x 3 x 2 x 1 = 120

Iteration versus Sets
#include <iostream>
#include <math.h>
using namespace standard;
int main()
{
for (int i=0;i<=5;i++) {
for (int j=-;j<=i;j++)
{
cout<< “ “<<j<<” “;
}
cout<< “nnn”;
return 0;
}

N+1 Problem
● N+1 Example
● You want a list of co-workers
who live near you and have a
car.
● SELECT EMPLOYEES
– Find those near you
● Then SELECT w/CAR
● Set Example
● Select employee near you and
have car
● One dive into data versus three!

Dump truck versus Pickup Truck Problem
● Database should do heavy
lifting
● Sort
● Statistical functions
● Your application should be a
scalpel not a machete
● - Select ONLY the columns
you need not all columns
● No SELECT *
● Think Data not Line

Heavy Lifting
for (Employee e in db.employees() )
if (e.department = “sales”)
e.salary = e.salary * 1.2
UPDATE Employees
SET salary = salary * 1.2
FROM Employees e
INNER JOIN Department d
ON (d.ID = e.Department)
WHERE d.name = 'sales'

Heavy Lifting
for (Employee e in db.employees() )
if (e.department = “sales”)
e.salary = e.salary * 1.2
START TRANSACTION;
UPDATE Employees
SET salary = salary * 1.2
FROM Employees e
INNER JOIN Department d
ON (d.ID = e.Department)
WHERE d.name = 'sales'
COMMIT;
Which do you thinks un-rolls easier???

Data Architecture
● Normalize your data
● General rule of thumb –
demoralization will get cost
later
– Time, $, sanity
● Use good naming
conventions CONSISTENTLY
● Use smallest practical data type
● You will not have 18 trillion
customers so do not make
customer_id a BIGINT
● Worst case data moves off
disk, into memory, onto net,
cross net, off net, into memory
– Pack efficiently

Indexes
● Index columns
● Found on right side of WHERE clause
● InnoDB will assign an index if you do not chose one
– And it may not choose the one your would really want!!
● Compound Index for common combinations
– Year-Month-Day works for searches on YMD, YM and Y
● But not D or MD

Books You Need NOW!!!
Effective MySQL: Optimizing
SQL Statement
Ronald Bradford
SQL Antipatterns
Bill Karwin

Heck with all this ..
● I will just use an ORM!!!
● Extra layer of complexity & overhead
● Need to make sure it is explicitly prefetching data
– N + 1 issues
● Often easier to just code good SQL

Code Example
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Code Example
<?php
$password = "secret";
// Check connection
}
?>
Possible
Security
Issue

Code Example
<?php
$password = "password";
// Check connection
}
?>
Who needs
To see this error.
Could end user
EXPLOIT?!?!

Example in PDO
<?php
try {
$conn = new PDO("mysql:host=$servername;dbname=mycorp", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>

<?php
$dbname = "mydata";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
}
$sql = "INSERT INTO customers (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

Prepared Statements<?php
$dbname = "mydata";
$conn = new mysqli($servername, $username,
$password, $dbname);
// Check connection
}
// prepare and bind
$stmt = $conn->prepare("INSERT INTO customers
(firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname,
$email);
// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();
?>

Why Prepared Statements?
● Efficiency
● Less parsing overhead
● Avoiding SQL Injection Attacks
– ALWAYS scrub user inputted data! Always!!!!Always!!!!

Example
<?php
...
$sql = "SELECT id, firstname, lastname FROM customers";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

Q/AQ/A● Slides at slideshare.net/davidmstokes
● @Stoker
● David.Stokes@oracle.com
● Opensourcedba.wordpress.com

SkiPHP -- Database Basics for PHP

More Related Content

Similar to SkiPHP -- Database Basics for PHP

More from Dave Stokes

Recently uploaded

SkiPHP -- Database Basics for PHP