-2

I've written a POS and back office system, and now after 18 months of customer use I'm looking to improve performance specifically with reporting.

The code is written using CakePHP version 2. I can't find any reference to using additional database indices other than the required Primary key on field "id". The data is stored on Amazon web servers, in MySQL - InnoDB database tables.

Does CakePHP encourage the use of additional indices? I did some background reading on SQL optimisation but I just don't know enough about SQL. With CakePHP there is no need to write any SQL so I'm assuming CakePHP handles all the optimisation itself, or perhaps creates dynamic indices as required.

Thanks in advance.

Robin Walmsley
  • 85
  • 1
  • 1
  • 10
  • Fair enough, thanks for taking the time to comment. – Robin Walmsley Nov 06 '19 at 16:18
  • You might like my presentation [How to Design Indexes, Really](https://www.slideshare.net/billkarwin/how-to-design-indexes-really), and the video of me presenting it: https://www.youtube.com/watch?v=ELR7-RdU9XU – Bill Karwin Nov 10 '19 at 17:59
  • And yes, for the record, any application framework such as CakePHP will benefit if you optimize your queries by adding indexes. The indexes must be chosen to support the specific queries you have in your app. – Bill Karwin Nov 10 '19 at 18:01
  • Thanks for your comments – Robin Walmsley Nov 11 '19 at 22:29
  • Just as a matter of interest, any idea why the question may have received negative marks? – Robin Walmsley Nov 11 '19 at 22:30
  • 1
    Stack Overflow is pretty explicit about the types of questions they want on this site. They want questions about solving _specific_ coding problems, not broad questions about so-called best practices. Those questions too often devolve into opinionated debates. You should read https://stackoverflow.com/help/how-to-ask and other articles in the Help Center. – Bill Karwin Nov 11 '19 at 22:34
  • I did not make a downvote, but I do understand why some people might downvote your question, because it isn't about any specific problem. – Bill Karwin Nov 11 '19 at 22:35
  • Is there a forum where I might ask general questions as this appears to be? I googled my question and was brought to Stack Overflow. – Robin Walmsley Nov 12 '19 at 10:28
  • Maybe reddit? I don't know of one where you can get a clear answer. They always devolve into endless opinionated discussions. I avoid sites like that. – Bill Karwin Nov 12 '19 at 15:06
  • 1
    Bill, just wanted to add, I watched your video - very informative thank you. I've had a quick go this morning at adding just a couple of indexes and already reduced the timing of one report from 6 seconds to 2 seconds. Very grateful for you input. Thanks. – Robin Walmsley Nov 13 '19 at 12:38

1 Answers1

2

CakePHP does nothing to automatically add indices to your tables, but your queries will absolutely benefit from doing so. Don't look for information about what will improve Cake's database performance, look for articles about improving the performance of MySQL queries in general.

Turn on query logging to get some examples of the queries that Cake is generating, that will give you things to describe, which articles about performance are very likely to want you to do.

Greg Schmidt
  • 5,010
  • 2
  • 14
  • 35