Level 122
use EXPLAIN and workout where you need to add indexes. For instance, it would be pretty silly if market_id is not indexed
I would also query by the column formats not convert them to unix timestamp
1 like
Nov 23, 2023
12
Level 2
How can I improve the performance of Query Builder?
laravel : 10
mysql 8.0
have 2 server mysql
1 for write 2 core , 4 Ram , 250GB NVME
1 for read 2 core , 4 Ram , 250GB NVME
in transactions table : 6 million record They are increasing on a daily basis
$table->boolean('is_maker')->index()->default(false);
$table->integer('market_id')->index();
$table->index(['created_at']);
database.php
'read' => [
'host' => [
env('DB_HOST_READ', env('DB_HOST', '127.0.0.1'))
],
],
'write' => [
'host' => [
env('DB_HOST', '127.0.0.1'),
],
],
'sticky' => true,
query This fetches data for the tradingView (datafeed api)
Execution time: 18 seconds
public function getCandles($market)
{
$from = intval(request()->get('from'));
$to = intval(request()->get('to'));
$limitGet = request()->get('countback', 200) > 330 ? 330 : request()->get('countback', 200);
$rangeInSeconds = $from - $to;
if ($rangeInSeconds > 3000000) {
$to = $from;
}
$resolution = request()->get('resolution', '60');
$interval = MARKET_RESOLUTION_ASSOC[$resolution] ?? false;
if (!$interval) {
return false;
}
// low price, high price, open price, close price, volume, timestamp sameh
$transaction = Transaction::query();
if ($market) {
$transaction->where('market_id', $market->id);
}
$transaction->maker()->whereRaw('UNIX_TIMESTAMP(created_at) > ?', $from)
->whereRaw('UNIX_TIMESTAMP(created_at) < ?', $to);
$transaction
->selectRaw("DATE_FORMAT(MIN(created_at), '%d-%m-%Y %H:%i:00') as date2")
->selectRaw('FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(created_at))/ ? ) * ?) AS date', [$interval, $interval])
->selectRaw('SUM(quote_currency) as volume')
->selectRaw('MAX(price) as high')
->selectRaw('MIN(price) as low')
->selectRaw("SUBSTRING_INDEX(MAX(CONCAT(created_at, '_', price)), '_', -1) as close")
->selectRaw("SUBSTRING_INDEX(MIN(CONCAT(created_at, '_', price)), '_', -1) as open");
$transaction
->limit($limitGet)
->groupByRaw('date')
->orderByRaw('date ASC');
return $transaction->get();
}
Are there any suggestions to improve the time, whatever these suggestions are, such as modifying the databases, transferring NoSql, or improving the code? Any idea that helps me improve the execution time?
Level 2
dd(DB::select("EXPLAIN " . $transaction->toSql(), $transaction->getBindings()));
array:1 [ // app/Repositories/Market/MarketRepository.php:271
0 => {#2116
+"id": 1
+"select_type": "SIMPLE"
+"table": "transactions"
+"partitions": null
+"type": "index_merge"
+"possible_keys": "transactions_is_maker_index,transactions_market_id_index,transactions_created_at_index"
+"key": "transactions_market_id_index,transactions_is_maker_index"
+"key_len": "4,1"
+"ref": null
+"rows": 248219
+"filtered": 99.999610900879
+"Extra": "Using intersect(transactions_market_id_index,transactions_is_maker_index); Using where; Using temporary; Using filesort"
}
]
$currencyLength = 36;
$currencyDecimals = 18;
Schema::create('transactions', function (Blueprint $table) use ($currencyLength, $currencyDecimals) {
// Fields
$table->bigIncrements('id');
$table->uuid('process_id')->index();
$table->uuid('order_id')->index();
$table->integer('user_id')->index();
$table->boolean('is_maker')->index()->default(false);
$table->integer('market_id')->index();
$table->string('order_type', 10)->index();
$table->string('order_side', 10)->index();
$table->decimal('price', $currencyLength, $currencyDecimals)->default(0);
$table->decimal('fee', $currencyLength, $currencyDecimals)->default(0);
$table->decimal('referral_fee', $currencyLength, $currencyDecimals)->default(0);
$table->decimal('base_currency', $currencyLength, $currencyDecimals);
$table->decimal('quote_currency', $currencyLength, $currencyDecimals);
// Timestamps
$table->timestamps();
$table->index(['created_at']);
});
select DATE_FORMAT(MIN(created_at), '%d-%m-%Y %H:%i:00') as date2, FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(created_at))/ ? ) * ?) AS date, SUM(quote_currency) as volume, MAX(price) as high, MIN(price) as low, SUBSTRING_INDEX(MAX(CONCAT(created_at,'_',price)),'_',-1) as close, SUBSTRING_INDEX(MIN(CONCAT(created_at,'_',price)),'_',-1) as open from `transactions` where `market_id` = ? and `is_maker` = ? and UNIX_TIMESTAMP(created_at) > ? and UNIX_TIMESTAMP(created_at) < ? group by date order by date ASC limit 330
array:6 [ // app/Repositories/Market/MarketRepository.php:271
0 => "86400"
1 => "86400"
2 => 21
3 => true
4 => 1672272000
5 => 1700784000
]
Level 122
so if your records are efficiently partitioned by date, and you have indexes on maker and market_id then the next candidates are the sum, min and max functions. Your query has to consider 250K rows and has no choice but to sum them order and select min+max
I would start by comparing the query time without these and then selectively adding them back in
I still worry about your dates. I don't know why you need to run functions against created_at and would strongly recommend passing the query the correctly formatted dates/times so that created_at index can be used directly
1 like
Level 2
$transaction->maker()->whereRaw('UNIX_TIMESTAMP(created_at) > ?', $from)
->whereRaw('UNIX_TIMESTAMP(created_at) < ?', $to);
I replaced it with this: ->
$transaction->maker()->where('created_at', '>', $from)
->where('created_at', '<', $to);
dd(DB::select("EXPLAIN " . $transaction->toSql(), $transaction->getBindings()));
array:1 [ // app/Repositories/Market/MarketRepository.php:274
0 => {#2116
+"id": 1
+"select_type": "SIMPLE"
+"table": "transactions"
+"partitions": null
+"type": "index_merge"
+"possible_keys": "transactions_is_maker_index,transactions_market_id_index,transactions_created_at_index"
+"key": "transactions_market_id_index,transactions_is_maker_index"
+"key_len": "4,1"
+"ref": null
+"rows": 249199
+"filtered": 49.999801635742
+"Extra": "Using intersect(transactions_market_id_index,transactions_is_maker_index); Using where; Using temporary; Using filesort"
}
]
As for this:
->selectRaw('FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(created_at))/ ? ) * ?) AS date', [$interval, $interval])
Use it for
->groupByRaw('date')
->orderByRaw('date ASC');
If there was an alternative, I would change it. I am not adhering to it being in this format During the inquiry
So if there is something better please help me with it and I will do it and the end result will be as:
if ($transactions && $transactions->count() > 0) {
$transactions->each(function ($transaction) {
// dd($transaction);
$this->candles['v'][] = $transaction->volume;
$this->candles['t2'][] = str_replace('.000000', '', $transaction->date);
$this->candles['t'][] = Carbon::parse(str_replace('.000000', '', $transaction->date))->timestamp;
$this->candles['o'][] = $transaction->open??0;
$this->candles['c'][] = $transaction->close??0;
$this->candles['h'][] = $transaction->high??0;
$this->candles['l'][] = $transaction->low??0;
});
$this->candles['s'] = 'ok';
} else {
$this->candles['s'] = 'no_data';
}
Thank you for trying to help me with this matter
Level 122
I would start by comparing the query time without these and then selectively adding them back in
1 like
Level 122
1 like
Level 2
@Snapey I used partitioned by date . I compared the query time without these items and then selectively adding them back in and there was very little difference in time
So I found that the best solution is to partitioned by date Now the execution time has gone from 18 seconds to 2 seconds, and I am still looking for solutions to reduce it somewhat. If you have any suggestions.
Level 41
Just a quick tip, the query builder already has the explain method. So you can simply do this:
YourModel::where(...)
->where(...)
->explain();
1 like
Level 41
Just FYI, regarding this statement:
$transaction->maker()->whereRaw('UNIX_TIMESTAMP(created_at) > ?', $from)
->whereRaw('UNIX_TIMESTAMP(created_at) < ?', $to);
Is maker a relationship? If so, that statement will create a totally new query instance that has nothing to do with $transaction. Pls remove that statement or do not concern it for now.
1 like
Level 14
$table->index(['created_at']); - this does nothing if you are querying UNIX_TIMESTAMP(created_at).
Your database might have an index for created_at but not for UNIX_TIMESTAMP(created_at), so the index cannot be used. Either format the date to the format of the created_at column or index UNIX_TIMESTAMP(created_at) instead of created_at.
1 like
Please or to participate in this conversation.