開発スタッフがアプリ関連の最新技術やニュースをご紹介します。

Here is some tips based on my experience to speed up database queries.

Table Indexing

Indexes are important part in database. Indexes are used to quickly retrieve data without having to search every row in a database table is accessed. The users cannot see the indexes, they are just used to speed up searches/queries.

How to use? For example, you have thousands of rows of users and you want to get an email.

Select * from users where email = “me@gmail.com”

Without index, this process will be from top to bottom until it the email is found.

Using with index, in Laravel, the easy way is to create a migration file.

public function up()
{
   Schema::table(‘users’, function(Blueprint $table)
   {
     $table->index(‘email’);
   });
}

Or if you currently on fresh project just add index to column you want to

public function up()
{
    Schema::create(‘users, function(Blueprint $table) {
      …
      $table->index(‘email’);
      …
    });
}

Index is not only for single column. You can use multiple column like this  $table->index([‘email’, ‘ phone_no’]); Let’s us look at a query that could use two different indexes on the table based on the WHERE clause restrictions. Multi column indexes are called compound indexes.

Eloquent Eager Loading

At its core Eager Loading, is telling Eloquent that you want to grab a model with specific relationships that way the framework produces a more performant query to grab all the data you will need. By eager loading, you can take many queries down to just one or two. Wrong implementation cause N+1. Here is where common developer do which cause N+1.

For example:

Class Post extends Model
{
  public function author()
  {
    return $this->belongsTo(Author::class);
  }
}

Lets fetch author about 100 rows from the table and what happen

Mistake 1

$post = Post::all();
foreach($posts as $post) 
{
  $total_user = $post->author;
}

The query runs once to get all the author and for each loop it query another query for author for 100 times. 100 + 1 = N+1 problem. Imagine if you want to fetch thousand to data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.

Solution

It can be solve by using with method in Eager loading. For example

$post = App\Post::with(‘author’)->get();

If you had multiple related associations, you can eager load them with an array:

$post = App\Post::with([‘author’, ‘comments’])->get();
$post =App\Post::with(‘author.profile’)->get();

With this, instead of hitting database N+1, with will single query by joining those table and transform it into collection. So you can freely access the collection object without hitting the database.

By Ami

Tweet

今回はUnityのUI Canvasの中から代表的なもの「InputField」「Button」「Text」の使用方法をシェアしたいと思います。

Tweet

TP-Linkから全ポート10GBASE-T対応のアンマネージドL2スイッチが
リリースされていました。

家庭用10ギガLAN環境が手軽に構築できます。

Tweet

When we are developing web systems using PHP, we might encounter to use magic methods in PHP a lot. Today I would like to talk about some important magic methods provided by PHP.

Actually there are lots of magic methods in PHP. For example,

• __construct()
• __destruct()
• __call($fun, $arg)
• __callStatic($fun, $arg)
• __get($property)
• __set($property, $value)
• __isset($content)
• __unset($content)
• __sleep()
• __wakeup()
• __toString()
• etc..

But we don’t use all of the things unless it’s necessary. I will only write about a few methods which are mostly used in my experience.

Construct

If you define this method in your class, it will be called automatically when an object is instantiated. The purpose of this method is to assign some default values to the properties of the object. This method is also called a constructor.

<?php
class Student {
    private $name;
 
    public function __construct($name) 
    {
        $this->name = $name;
    }
}
 
$student = new Student('yuuma');
?>

Destruct

The __destruct () method is called destructor and is called when the object is destroyed. Generally also called when script stops or exits. The purpose of this method is to provide an opportunity to save the state of the object or any other cleaning you want to do.

<?php
class Student {
    private $name;
 
    public function __construct($name) 
    {
        $this->name = $name;
    }
 
    public function __destruct() 
    {
        echo 'This will be called when the class has been closed';
        // file close, removing session etc.
    }
}
 
$student = new Student('yuuma');
?>

Set

The magic __set () method is called when you try to set data on properties of inaccessible or non-existent objects. The purpose of this method is to set additional object data for which you have not explicitly defined object properties.

<?php
class Student {
    protected $data = array();
 
    public function __set($name, $value) 
    {
        $this->data[$name] = $value;
    }
}
 
$student = new Student();
//  __set() called
$student->name = 'yuuma';

As you can see from the example above, we are trying to set the property of the name, which does not exist. And so the __set () method is called. The first argument to the __set () method is the name of the property that is being accessed, and the second argument is the value we are trying to set.

Get

In the case of the __set () method example in the previous section, we discussed how to set values for non-existent properties. The __get () method is the exact opposite. The magic __get () method is called when you try to read data from properties of inaccessible or non-existent objects. The purpose of this method is to provide values for these properties.

<?php
class Student {
    private $data = array();
 
    public function __set($name, $value) 
    {
        $this->data[$name] = $value;
    }
 
    public function __get($name) 
    {
        If (isset($this->data[$name])) {
            return $this->data[$name];
        }
    }
}
 
$student = new Student();
 
//  __set() called
$student->name = 'yuuma';   
 
//  __get() called
echo $student->name;                      
?>

I would like to write more but I think the article is a little bit long for now. So I will write a part 2 in next week relating with this week article. So see you next week.

By Yuuma

Tweet

Hello Everyone,
I am ami, which is my Japanese name. My actual name is Khin Moh Moh Naing.
I am a web developer, loves to fix bugs and passionate in learning new technologies.

I graduated from Computer University from Mandalay in Myanmar with a degree in Computer Science.
Since I graduated from university, I am continuously working as a web developer.
I have been working in the industry of technology for over 2 years with the languages PHP and Laravel.

At the end of 2020 I started to join Gigas Japan and it’s such a great pleasure to meet you all.

One of my dreams is to work in Japan and want to study modern technologies and Japanese working culture.
In the future, I am also interested in Mobile Development. Currently I am working in web related system.
I am not very well communication in Japanese language but I will keep learning Japanese and try to improve my Japanese skills.
I will be writing tech related articles every Friday.

It’s nice to meet to you again everyone. よろしくお願いいたします。

By Ami

Tweet