If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > How to improve index on multiple columns

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 32
How to improve index on multiple columns

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?

Last edited by akrashdi; 11-12-12 at 08:25. Reason: more experiments
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
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.
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 32
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' )

Last edited by akrashdi; 11-15-12 at 11:14. Reason: new considerations, made the changes, forgot to mention
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On