textpattern/textpattern — pull request #4 by jacques (2013-11-04 08:27:49+00:00)
Title: Start of work to stop TextPattern doing full table scans and reading 70million+ rows from disk.

I have started to actually make more notes while working through some shared hosting performance issues on TextDrives Shared Hosting service, to make sure that the schema changes being made to fix pain points get passed back upstream and not getting stuck in Confluence/JIRA as they have been in the past at Joyent. As I keep having to do this sort of thing on a regular when I find MySQL performance issues, it is beneficial that these get pushed upstream for all TextPattern users to help from speedups. These changes I am making each time I meet a TextPattern blog causing issues.

# txp_log

TextPattern is doing full table scans and reading 30million+ rows from disk for this particular users txp_log table. It does not use any indexes due to the WHERE being on a column where there is no index present:

````
mysql> EXPLAIN select host from txp_log where ip='66.249.75.159' limit 1;
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | txp_log | ALL | NULL | NULL | NULL | NULL | 3079741 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
````

Please let me know if you have any questions, comments or concerns about pulling these changes upstream into the official textpattern code base.

Where do you track schema changes for the upgrade scripts? The schema changes need to be made there as well.