Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011

    Unanswered: How to improve index on multiple columns


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

    Explain SELECT title, data, url, direction   
    		FROM content 
    		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:
    	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?



    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 09:25. Reason: more experiments

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    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)
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2011
    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?



    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.

    CREATE TRIGGER yearmonth
    ON pages
    SET NEW.yearmonth = DATE_FORMAT( CURRENT_TIMESTAMP ,  '%Y%m' )
    Last edited by akrashdi; 11-15-12 at 12:14. Reason: new considerations, made the changes, forgot to mention

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts