MySQL table scans in concrete5
Permalink
It looks to me like we have a few table scans. Some of them are executed regularly and should probably be fixed. There are a couple of scans I'm not sure how to fix though.
Here the problem is that cPath isn't VARCHAR but rather TEXT. We could switch to VACHAR which has a max length of 65,535 if we use MySQL 5.0.3 or higher but we can't add an index for such a column. Having a maximum path length of 255 seems to be a bit dangerous. Any ideas on how we could avoid a table scan here?
The same happens in this query as well:
I have a site with more than 10'000 pages and this is a bit of a problem. Unfortunately cache isn't an option in this case..
---
This is probably not really an issue as AttributeTypes is always going to be a very small table...
SELECT cID FROM PagePaths WHERE cPath = '/dashboard/scrapbook' LIMIT 1
Here the problem is that cPath isn't VARCHAR but rather TEXT. We could switch to VACHAR which has a max length of 65,535 if we use MySQL 5.0.3 or higher but we can't add an index for such a column. Having a maximum path length of 255 seems to be a bit dangerous. Any ideas on how we could avoid a table scan here?
The same happens in this query as well:
SELECT cID FROM PagePaths WHERE cPath = ? AND cID <> ?
I have a site with more than 10'000 pages and this is a bit of a problem. Unfortunately cache isn't an option in this case..
---
SELECT atID, pkgID, atHandle, atName FROM AttributeTypes WHERE atHandle = 'text'
This is probably not really an issue as AttributeTypes is always going to be a very small table...
