I've used a php/mysql script on several sites for a few years now, I've hosted them on 3 different VPS's, a dedicated server for a year, and on my PC as a test. In all cased, even with very little information in the database and no traffic - it runs slow. 2-5 seconds per page. The index being especially slow.
I've spent the better part of a week now trying to figure out how to understand this better. I realize that it may not be possible for a person with my (lack of) knowledge to re-write a script and make it faster.
So instead I've decided that my first goal will be to simply understand what makes it go slow in the first place.
This is the totals from the main page on my test. Note, that if I turn on several options (that I would really like to use) the page load time increases by multiple seconds. So the below query is actually using as few queries as possible and still be functional.
15 total queries
This page was created in 2.59 seconds
Memory used: 5877040 bytes
load average: 1.93, 1.49, 1.21
and every so ofter (1 out of 7-10 page views) I'll get something like the following
15 total queries
This page was created in 5.58 seconds
Memory used: 5877528 bytes
load average: 1.32, 1.25, 1.11
PHP Version: 4.3.10
MySQL Version: 3.23.58
Here is some other information
Slow queries: 0
Flush tables: 1
Open tables: 64
Queries per second avg: 7.839
I can also turn on some other debugging to have it display the sql statements and execution time as they are executed.
I've tried running the individual queries with EXPLAIN SELECT, but they seem to execute fairly quickly. I've tried adding new indexes in needed areas. But this has also made very little difference (a fraction of a second perhaps).
After thinking more about the "memory used" and looking at top on my server I'm not starting to think that this is more than a simple mysql issue.
What are some of the areas I should investigate next? What are some topics that I need to focus my research in? What other information can I provide to ask this a better way?