Level 4
What's your use case for that code snippet? Does it run inside a migration because I don't see any other use for it? Did you test with a fresh laravel install and noticed the same issue?
Feb 21, 2024
10
Level 1
Slow Performance with Schema::hasTable() After Laravel 10 Upgrade - Seeking Guidance on Optimization
Hi Laracasts community,
I hope you're all doing well. I recently upgraded my Laravel application from version 9 to version 10, and since then, I've been experiencing performance issues specifically related to the use of Schema::hasTable().
Here are some key details about the issue:
- Laravel Version Before Upgrade: 9
- Laravel Version After Upgrade: 10
- Database Engine: MySQL
- Specific Problem: The slowdown occurs when using Schema::hasTable('my_table').
I've already considered caching the results and ensured that the database is properly indexed. However, the performance impact is still noticeable since the upgrade.
// Example code snippet
if (Schema::hasTable('my_table')) {
// Table exists logic
}
I wanted to reach out to the community to seek advice and guidance, especially from those who have recently upgraded to Laravel 10. If anyone has encountered similar issues or has suggestions on optimizing Laravel database operations post-upgrade, I would greatly appreciate your insights.
Questions:
- Are there known performance considerations or changes introduced in Laravel 10 that might impact the use of Schema::hasTable()?
- How can I effectively profile and identify the root cause of the performance slowdown in the context of a Laravel version upgrade?
- Are there Laravel 10-specific optimizations or configurations that might help improve database-related operations?
Any advice, tips, or experiences shared would be incredibly valuable. Thank you in advance for your time and assistance!
Level 1
if(Schema::hasTable('users')) {
$user = DB::table("users")->get();
}
This is my scenario. I'am checking if the table exists before performing the db operation. This was working fine till Laravel 9. After upgraded to Laravel 10, the same checking is taking too much time. Like about 5 seconds.
And i have find a possible root cause of the issue.
This is the hasTable function from Illuminate/Database/Schema/Builder.php from Laravel 9.
public function hasTable($table){
$table = $this->connection->getTablePrefix().$table;
return count($this->connection->selectFromWriteConnection(
$this->grammar->compileTableExists(), [$table]
)) > 0;
}
This was working fine.
And, This is the hasTable function from Illuminate/Database/Schema/Builder.php from Laravel 10.
public function hasTable($table)
{
$table = $this->connection->getTablePrefix().$table;
foreach ($this->getTables() as $value) {
if (strtolower($table) === strtolower($value['name'])) {
return true;
}
}
return false;
}
This method is using a foreach to iterate through all the tables which is causing a big delay and load to the database.
I think laravel has to fix this issue asap
Level 73
@Ajayp Why on earth would you need to check something like that?
If you are creating tables programatically you are in deep shit.
1 like
Level 1
@Tray2 What?
I already mentioned, this is what i need to do.
if(Schema::hasTable('users')) {
$user = DB::table("users")->get();
}
But as i mentioned earlier, since my database has a huge number of tables, this method is taking much time after upgrading to Laravel 10. The possible reason for this delay is what i indicated in earlier post.
Level 122
@Ajayp But this is not the sort of function you should need to execute as part of the normal running of the application.
You mean you are not sure if you have a users table?
And then you get ALL users from the table and put them in a variable called $user ?
I can see lots of potential to improve the performance of your code other than this one check.
have you tried instead using try-catch? Not sure what your code is meant to do if the table is missing?
Level 4
@Ajayp Have you tried switching to models/migrations instead of guessing which table exists and or not? There's no point on using a framework if you don't use all the tools it provides.
FYI, this is the query that runs under the hood. If you have milions of tables or large tables, it might run slow.
select table_name as `name`, (data_length + index_length) as `size`, table_comment as `comment`, engine as `engine`, table_collation as `collation` from information_schema.tables where table_schema = 'YOUR_DB_HERE' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED') order by table_name
Level 73
Level 1
The code that i posted in my question was just an example :( . Why you guys get confused with that. I simply explained a scenario.
I will tell my exact use-case.
We are running a SaaS product where around 10000 companies are enrolled. Each company will have around 40 tables under their prefix. We started the project around 5 years ago. So some of the old companies does not have all the 40 tables due to many reasons.
There are many scenarios in our project where it is needed to check if the table exists before doing the operation and in some cases, if not exist, create them.
So Schema::hasTable() seemed to be a good option and it served the purpose for the last 4 years. The slowness issue started when we updated to laravel 10.
Hope you guys understood the scenario.
Level 51
Everybody's points above are still valid, @Ajayp. Just because some of your companies don't have access to certain features doesn't mean the underline database tables shouldn't exist. Use feature flags, use Laravel pennant, use anything else but conditionally creating database tables.
There's a reason why nobody else does this... make life easy for yourself, not harder
Level 73
@jaseofspades88 Agreed, the database should look exactly the same for each company, and I would suggest that you don't have a table for each supplier, but rather use a single table for all companies, and then handle it via a tenant scope. If you are fearing performance issues, you can partition the tables for each company, and that is the same as using seperate tables for each company as far as the database is concerned.
1 like
Please or to participate in this conversation.