Customer Portal

Get Involved. Join the Conversation.

Topic

    Nisar
    "Too many rows examined" for a mere 115 rows?Answered
    Topic posted October 24, 2016 by NisarBronze Crown: 15,000+ Points, last edited October 24, 2016 
    270 Views, 6 Comments
    Title:
    "Too many rows examined" for a mere 115 rows?
    Content:

    One of the ROQL queries that I was using has suddenly stopped working today with the following message:

    'Poor performing query - too many rows examined'

     When I did a count of number of rows being returned in "Connect Object Explorer" section of the portal, I got 115 rows back. It's strange that such a low number is throwing that exception.

    My ROQL query isn't querying for any unnecessary data so I'm not sure how I can optimize it further. Here is what the query looks like:

    $sql = "SELECT I.Product.LookupName AS product_name, I.Product.ID AS product_id, I.Category.ID AS category_id, I.StatusWithType.Status.ID AS status_id, I.CreatedTime AS created, I.CustomFields.c.case_subtype.ID AS subtype_id, I.ClosedTime AS closed FROM Incident I WHERE CustomFields.c.division.ID = 632 AND (CreatedTime BETWEEN '2016-10-01T00:00:00-04:00' AND '2016-10-31T23:59:59-04:00') ORDER BY Product.LookupName ASC"
    $query = RNCPHP\ROQL::query($sql)->next(); // this is throwing the exception
    

    Is there something I'm missing in terms of optimization of the query? Also this was working two days back. Not sure what caused it to break suddenly.

    Version:
    3.2.6

    Best Comment

    Allan Schrum

    The number of rows depends upon the joins and tables being examined. First, how many incidents are there in that date range? Next, you are selecting I.Product.ID which implies a join to ServiceProduct to get the ID column. I.Product.LookupName implies a join to ServiceProduct to get the LookupName which joins with the labels table. The custom fields (using package "c", the old technology) uses the "ID" column which joins with the menu_items and labels. As you can see, this can branch out rather quickly and imply examining many rows. Lastly, much of this depends upon which index MySQL chooses to perform the query. It might be that the "CreatedTime" index is used and examines X number of rows, or it chooses the index on the custom field and examines Y number of rows.

    For the "too many rows" examination MySQL calculates an approximation of how many rows will be examined using the available indices (more accurately, the index chosen with the best apparent statistics) and the tables joined against the main table. As the system operates the statistics on the indices of the table are updated and thus the decisions made by the MySQL optimizer might change from one day to the next which might lead to differences in how the query is processed.

    In this situation, I'd try setting the CreatedTime window to a smaller range to influence how MySQL processes the query.

    Regards,

    -Allan

    Comment

     

    • Tony William

      Hi Nisar,

      I think its indexing problem, select check Indexed for your custom Field and try

    • Nisar

      Hi Tony,

      Thanks for the suggestion. It was my first to-do thing too; have already sent a request to a team member to index the column.

      However, what baffles me is the sudden appearance of this exception. Also, the error message also seems quite misleading as querying for 115 rows shouldn't have said that (can understand if that number was in thousands)

    • Allan Schrum

      The number of rows depends upon the joins and tables being examined. First, how many incidents are there in that date range? Next, you are selecting I.Product.ID which implies a join to ServiceProduct to get the ID column. I.Product.LookupName implies a join to ServiceProduct to get the LookupName which joins with the labels table. The custom fields (using package "c", the old technology) uses the "ID" column which joins with the menu_items and labels. As you can see, this can branch out rather quickly and imply examining many rows. Lastly, much of this depends upon which index MySQL chooses to perform the query. It might be that the "CreatedTime" index is used and examines X number of rows, or it chooses the index on the custom field and examines Y number of rows.

      For the "too many rows" examination MySQL calculates an approximation of how many rows will be examined using the available indices (more accurately, the index chosen with the best apparent statistics) and the tables joined against the main table. As the system operates the statistics on the indices of the table are updated and thus the decisions made by the MySQL optimizer might change from one day to the next which might lead to differences in how the query is processed.

      In this situation, I'd try setting the CreatedTime window to a smaller range to influence how MySQL processes the query.

      Regards,

      -Allan

    • Anuj Behl

      Hi Nisar,

      I think the error 'Too many rows examined' refer to the records system had to examine to get the resultant rows. For example, You may get only 1 row as a result of some query but the system might have examined a million rows to get you that one row. 

      -Anuj

    • Rajan Davis

      I am not sure of your use case, but I have found that sometimes you get a little better performance from creating a report and then using the ConnectPHP API to run the results. This isn't always the case, but looking at your query, I can't see anything that would really optimize it.

      I would create a report with the columns you looking for above (you can label them just like your aliases), hard code the custom field filter to the value you have above, and hard code the date range for the date created. You can run the analytics using ConnectPHP without any filters.

      Additionally, I would try the dates so that the end date is not past the present date; I doubt that this would be the source of your issue, but I have had some weird errors with reports and date ranges that go into the future.

    • Nisar
      Rajan Davis said:

      I am not sure of your use case, but I have found that sometimes you get a little better performance from creating a report and then using the ConnectPHP API to run the results.View original

      That's a good suggestion. Might take that route from here on out.