Results 1 to 3 of 3
  1. #1
    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

  2. #2
    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

  3. #3
    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

Posting Permissions

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