How to improve index on multiple columns

akrashdi · #1 (**permalink**) 11-12-12, 08:03

Hi,

I have index on multiple columns to get records based on multiple conditions as:

Code:

Explain SELECT title, data, url, direction   
		FROM content 
		WHERE 
		member_id = 1
		AND year = 2012
		AND month = 11 
		AND type='member'
		AND status=1
		order by content_id DESC 
		LIMIT 2000, 5

This is the explanation I get:

Code:

	id_home_status,index_id_year_month_type_status	index_id_year_month_type_status	87	const,const,const,const,const	5001	Using where

The indexed columns are (member_id, year, month, type, status), and content_id is the Primary Key.

There are 0.5M+ records in a table, the query is taking around 20 seconds. What changes should I make to get the results faster?

Thanks,

-----------------

I did more experiments and found out that the 'offset' of LIMIT clause is causing all the delay, for example using LIMIT 100, 5 took about 500ms. So, what's the best way of using Limit clause?

it-iss.com · #2 (**permalink**) 11-12-12, 09:56

Try adding contentid to the index as follows (member_id, year, month, type, status, contentid). This way it will already have the content ordered for you and search for the 2000th record and take the 5 after that.

akrashdi · #3 (**permalink**) 11-12-12, 13:05

I could not come up with any idea how to search for that 2000th row. I thought of computing it with the help of 'page no' and 'page-size' variables, but something like:

5 rows from 300040 and .... then 5 rows from 300070.

won't work because there are many rows from other members in between.

Then I googled and found out that Limit clauses's offset parameter is a big performance issue. I have such structure that it lets user go onto Nth page, which I want to keep since only these pages have inbound links to articles.

So, only thing that comes to my mind is that usually users don't go beyond certain number of pages, and query for earlier pages are not that slow, then I have caching system in place, which will cache the page on first query run.

Mr. Ronan what you think could be done for better results?

Thanks

---------------

In order to support this paginated design, the considerations I have are:

Cache the results (obviously)

Limit # of posts a user can post per month (a couple of hundreds)

Avoid long navigation by increasing page size so that queries are run less often

---------------

Yup, I did this and the results are very reasonable, also I have merged year and month columns i.e. '201211';

--------------

Forgot to mantion that I had to create this trigger for the merged 'yearmonth' column since DEFAULT value can only be a constant.

Code:

CREATE TRIGGER yearmonth
BEFORE INSERT 
ON pages
FOR EACH ROW 
SET NEW.yearmonth = DATE_FORMAT( CURRENT_TIMESTAMP ,  '%Y%m' )

Internet Services

Web Development

Digital Marketing

Consumer Tech