Query Optimisation Help

Databases
1

Hi,

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 :slight_smile:

2

What does an explain on the query read out? Do you have any combined indexes?

3

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

4

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.