Rick,
>One technique for improving things is to "vertically partition" a wide table (like jos_content) -- Meta data in one table (narrow, well indexed); bulky data in another table (with just an id). Then do a JOIN where one 'table' is a subquery finding the ids that are desired.
How would you justify what you suggested i.e. 'vertically partition'? if we look at provided EXPLAIN output, the query is doing tight index scan (no temporary table). There should be no need to touch the MEDIUMTEXT/TEXT fields for this query, so they should not impact performance any. It shouldn't need to touch anything except the index on (idx_state):
+----+-------------+-------------+-------+---------------------------------+-----------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------------------------+-----------+---------+------+-------+-------------+
| 1 | SIMPLE | jos_content | range | idx_section,idx_state,idx_catid | idx_state | 1 | NULL | 38791 | Using where |
| 1 | SIMPLE | sm | ALL | idx_secid,idx_secshowarchive | NULL | NULL | NULL | 10 | Using where |
| 1 | SIMPLE | cm | ALL | idx_catid,idx_catshowarchive | NULL | NULL | NULL | 81 | Using where |
+----+-------------+-------------+-------+---------------------------------+-----------+---------+------+-------+-------------+
3 rows in set, 1 warning (0.02 sec)
Edited 1 time(s). Last edit at 07/12/2012 08:14AM by Aftab Khan.