Back about 15 years ago, I worked for a web hosting company that provided some sysadmin consultation services. Customer paid us, and I would take a look.
I had one customer who came back with the same request, slightly differently worded, every single month, and every single month I'd say the same thing. They had this site they were running that was essentially a Yellow Pages type site. They had a large set of companies with contact details, each with multiple business categories associated with it. You'd choose a category, and they'd return a list of matching companies.
The problem was the site was really slow. I took a quick look around, and saw that all the time was lost querying the database. Taking a quick look at the schema I discovered that their approach to categorisation was to have a TEXT column, with semicolon separated 4 character strings in it. Each 4 character string mapped to a business category.
So when someone wanted to load up, say, all pest control companies, it would check the category mapping table, get the 4 character string, and then go to the companies table and do:
SELECT * FROM companies WHERE categories LIKE "%PEST%"
So on each page load of the main page type the site was there to provide, it did a full text search over the category field for every single record in the company table.
I guess that's probably okay for the developer without real world scale data, and real world traffic counts to worry about. But they had lots of data in the database, and that category field could have dozens of categories against a company. As soon as they had more than about 4-5 simultaneous customers performance started tanking.
I could never get them to accept that they needed to rethink the database schema. One month they were bleating about how is it possible that Google can manage to do such a search across a much larger amount of data, much faster. They really didn't like my answer that amounted to "By having a sane database schema". All they were willing to do was pay over the odds for our most powerful server at the time, which had enough capacity to hold the entire database in memory.
I stumbled across that comment a few years back and it changed the way I handle tags and categories so just sharing it here. If anyone has an equivalent for Sqlite, I’d love to hear it!
That's what my suggestion came down to. A two column table with company id and category id. They had ids already. They could index off category and get the results in split seconds
I had one customer who came back with the same request, slightly differently worded, every single month, and every single month I'd say the same thing. They had this site they were running that was essentially a Yellow Pages type site. They had a large set of companies with contact details, each with multiple business categories associated with it. You'd choose a category, and they'd return a list of matching companies.
The problem was the site was really slow. I took a quick look around, and saw that all the time was lost querying the database. Taking a quick look at the schema I discovered that their approach to categorisation was to have a TEXT column, with semicolon separated 4 character strings in it. Each 4 character string mapped to a business category.
So when someone wanted to load up, say, all pest control companies, it would check the category mapping table, get the 4 character string, and then go to the companies table and do:
So on each page load of the main page type the site was there to provide, it did a full text search over the category field for every single record in the company table.I guess that's probably okay for the developer without real world scale data, and real world traffic counts to worry about. But they had lots of data in the database, and that category field could have dozens of categories against a company. As soon as they had more than about 4-5 simultaneous customers performance started tanking.
I could never get them to accept that they needed to rethink the database schema. One month they were bleating about how is it possible that Google can manage to do such a search across a much larger amount of data, much faster. They really didn't like my answer that amounted to "By having a sane database schema". All they were willing to do was pay over the odds for our most powerful server at the time, which had enough capacity to hold the entire database in memory.