If you're at the point with your application that a million rows is slowing you down, it's probably not the underlying database server that will be the source of your trouble. A lot of the time, slow queries come down to inefficient table design and in particular poor indexes.
I know @jordan mentions that Facebook are looking for alternatives and Google no longer use MySQL, but you have to remember these guys are dealing with orders of magnitude more data than most of us will be dealing with, and at that point, you'll have the staff behind you to more or less build your own database solution.
Now taking your example, @ElMorin, you'll have three tables: clients, users, dealers, with their basic structure as follows:
dealers - id, name
users - id, dealer_id, name, email, password
clients - id, user_id, name
From this, we can build three models and start to derive relationships:
namespace App;
use Illuminate\Database\Eloquent\Model;
// app/Dealer.php
class Dealer extends Model
{
protected $fillable = [ 'name', ];
public function users()
{
return $this->hasMany(User::class);
}
}
// app/User.php
class User
{
public function dealer()
{
return $this->belongsTo(Dealer::class);
}
public function clients()
{
return $this->hasMany(Client::class);
}
}
// app/Class.php
class Client
{
protected $fillable = [ 'name', ];
public function user()
{
return $this->belongsTo(User::class);
}
}
With the relationships you've established, you don't need a pivot table as I understand it. Your relationships are all one to many at this stage. The pivot table will come into it if a user belongs to many dealers or a client belongs to many users, but I don't see this need at the moment.
Now, if you needed to find any of these specific records, you can use the Eloquent methods:
$dealer = App\Dealer::find(1); // SELECT * FROM dealers WHERE dealers.id = 1 LIMIT 1;
$user = App\User::find(5); // SELECT * FROM users WHERE users.id = 5 LIMIT 1;
$client = App\Client::find(3); // SELECT * FROM clients WHERE clients.id = 3 LIMIT 1;
As you can see, any of these is only running a single query - it doesn't (and wouldn't, even with the pivot table) need to hit any other tables to get a single record.
If you wanted to load all users for a dealer when you load the dealer, you could do something like the following:
$dealer = App\Dealer::with('users')->find(1);
// Executes two queries:
// 1. To fetch the dealer record - SELECT * FROM dealers WHERE id = 1 LIMIT 1;
// 2. To fetch the associated users - SELECT * FROM users WHERE users.dealer_id IN ( 1 );
Now, if you needed to get all users and clients in one go, you could just add the extra relationship to your Eloquent model:
$dealer = App\Dealer::with('users.clients')->find(1);
// A third query is run - SELECT * FROM clients WHERE clients.user_id IN ( $ids_from_clients_result )
All up, you're executing three queries to get all of your related data for any given dealer.
Alternatively, you can define a has many through relationship on the dealer for your clients:
class Dealer
{
public function clients()
{
return $this->hasManyThrough(Client::class, User::class);
}
}
$dealer = App\Dealer::with('clients')->find(1);
// 1. SELECT * FROM dealers WHERE dealers.id = 1 LIMIT 1
// 2. SELECT clients.*, users.dealer_id FROM clients INNER JOIN users on users.id = clients.user_id WHERE users.dealer_id in ( 1 )
What this will let you do is grab all clients that belong to the top-level dealer, without having to hit the intermediary (users) table, and cuts the number of queries down to two. If you wanted to also get the owning user for each client, you can amend that to:
$dealer = App\Dealer::with('clients.user')->find(1);
Doing so will add the extra eager load as with the original App\Dealer::with('users')->find(1) query.
$dealer->clients->each(function ($client) {
sprintf('%s | %s | %s%s', $client->user->dealer->name, $client->user->name, $client->name, PHP_EOL));
});
Quitzon Ltd | Kelton Brown | Aurore Ledner
Quitzon Ltd | Kelton Brown | Tressie Beier
Quitzon Ltd | Kelton Brown | Maci Boyer
...
I've committed this sample stuff to GitHub if you want to look into it a bit more, there's some model factories that will build up your database easily.
Hope this helps :)
