John Davidson

Message:

Question Edit

Server

32 GB RAM

innodb_buffer_pool_size=1400M

Database

Fresh empty table:

fields(id, name, type, optional)

SHOW TABLE STATUS LIKE "fields" result

Name:fields|Engine:InnoDB|Row_format:Dynamic|Rows:0|Av_row_length:0|Data_length:16384|Max_data_length:0|Data_free:0|Auto_increment:null|Create_time:2022-08-01 12:52:42|Update_time:NULL|Check_time:NULL|Collation:utf8_general_ci|Checksum:NULL|Create_options:|Comment:|Max_index_length:0|Temporary:N

Test code

Route::get('/', function () {

    $gtc = microtime(true);

    DB::connection()->getPdo();

    $total = round((microtime(true) - $gtc) * 1000);

    echo "Laravel Connection time: $total ms<br>";



    $gtc = microtime(true);

    DB::select('SELECT * FROM fields');

    $total = round((microtime(true) - $gtc) * 1000);

    echo "Laravel Get rows time: $total ms<br>";



    $gtc = microtime(true);

    DB::select('SELECT 1');

    $total = round((microtime(true) - $gtc) * 1000);

    echo "Laravel SELECT time: $total ms<br>";



    $gtc = microtime(true);

    $pdo = new MySQLModel(env('DB_HOST'), env('DB_DATABASE'), env('DB_USERNAME'), env('DB_PASSWORD'));

    $connection = $pdo->getConnection();

    $total = round((microtime(true) - $gtc) * 1000);

    echo "PDO Connection time: $total ms<br>";



    $gtc = microtime(true);

    $ps = $connection->prepare('SELECT* FROM fields');

    $ps->execute();

    $total = round((microtime(true) - $gtc) * 1000);

    echo "PDO Get rows time: $total ms<br>";



    $gtc = microtime(true);

    $ps = $connection->prepare('SELECT 1');

    $ps->execute();

    $total = round((microtime(true) - $gtc) * 1000);

    echo "PDO SELECT time: $total ms<br>";



    for($i = 0; $i<5; $i++) {

        print_r(DB::select('SELECT NOW(3)'));

        echo '<hr>';

    }

});

Results

Laravel Connection time: 549 ms

Laravel Get rows time: 136 ms

Laravel SELECT time: 137 ms

PDO Connection time: 282 ms

PDO Get rows time: 68 ms

PDO SELECT time: 68 ms

Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:10.784 ) ) 

Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:10.921 ) ) 

Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.057 ) ) 

Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.193 ) ) 

Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.333 ) ) 

This does not happen if the database is located in the same server but when connecting with the remote one.

It all points it is a connection issue related with Eloquent and I haven't found any information about configuring something to prevent such a huge increase in query execution (tends to doubles it in all test I have made).

---

My Answer:
The slowness of your Eloquent query could be due to a variety of factors, including network latency, server performance, or inefficient query execution. Here are a few suggestions to improve the performance of your query:

1. Indexing: Make sure that you have proper indexes set up on the columns that you are querying. In this case, you may want to consider adding an index on the "name" column if you are frequently querying based on that field.

2. Eager Loading: If you are fetching related models along with the main model, make sure to use eager loading to reduce the number of queries being executed. This can significantly improve performance, especially when dealing with large datasets.

3. Query Optimization: Review your query and make sure that it is optimized for performance. Avoid using unnecessary functions or operations that could slow down the query execution.

4. Caching: Consider implementing caching mechanisms to store the results of your query and avoid executing it multiple times. This can be particularly useful for queries that are frequently executed with the same parameters.

5. Server Configuration: Check your server configuration settings, such as the MySQL configuration parameters, to ensure that they are optimized for performance. You may need to adjust settings such as the innodb_buffer_pool_size based on the available resources and the size of your dataset.

By implementing these suggestions and optimizing your query and server configuration, you should be able to improve the performance of your Eloquent query when using a remote database.