I'm currently working on a project that structures content using a versioning system. There are 2 database tables that make up this structure:
wr_items - This holds the core information about an individual item, such as it's latest status, date created and last edited timestamp, it's unique ID etc.
wr_item_version - This table has an entry for each version of an item that is saved. This table includes the core information for each version, such as the version's title, timestamp, status, meta information etc.
As content has been entered into the system (there are almost 2,000 items, which have around 8,000 versions associated with them) fetching lists of items from the DB using PHP has become quite intensive and can take 10-15 seconds purely on database queries. I have already looked into optimising the queries before this point, however thought I'd post here to see if I can get any more advice on them.
The main query that is taking time is the following, on some pages multiple "types" are required so this query can end up being run 4 or 5 times in one page load with a different "type" value in the where clause:
SELECT a.* FROM wr_item_version a, wr_items b, (SELECT item, max(ts) AS max_ts FROM wr_item_version WHERE `status` != 'New' GROUP BY item) c WHERE a.item = b.id AND a.item = c.item AND a.ts = c.max_ts AND a.`type` = 'holiday' AND b.`gstatus` != 'New' AND a.`trashed` != '1' ORDER BY a.ts DESC LIMIT 20, 20
There are indexes set up on the status, type, timestamps, gstatus, trashed and item fields in the tables. I refined my previous method which included a lot of PHP loops to query the database many times in order to get the same information which took even longer to execute.
I'm happy to post any more information about the tables if required, any advice would be greatly appreciated
What does an explain on the query read out? Do you have any combined indexes?
Running an explain gives this as a result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2109 Using temporary; Using filesort
1 PRIMARY b eq_ref PRIMARY,gstatus PRIMARY 4 c.item 1 Using where
1 PRIMARY a ref item,type,trashed,ts item 4 web165-tfifty5.b.id 1 Using where
2 DERIVED wr_item_version index status item 4 NULL 9476 Using where
There's no combined indexes set up
Can you supply a little bit more about your table design along with some sample data and what some common operations / goals of the setup. I have this feeling that most of the problem can be resolved with a slightly different table design.