MySQL choosing wrong indexes

  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL choosing wrong indexes

    Hello, I'm hoping someone can point me in the right direction on this. I've had trouble searching because I don't quite know what this sort of problem is officially called. We're running MySQL 5.5.9 with four slaves and one master. I've been running into a problem where one or more of the slaves will choose the wrong indexes on fairly complicated queries.

    For instance, with the same query, the first row on the explain will be:

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: wrsemployees
    partitions: NULL
    type: ref
    possible_keys: PRIMARY,wrsemployees_practice_id
    key: wrsemployees_practice_id
    key_len: 5
    ref: const
    rows: 24
    Extra: Using where; Using index; Using temporary; Using filesort

    While on another server, it will be:
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: wrsemployeeordersnotesections
    partitions: NULL
    type: ALL
    possible_keys: PRIMARY,wrsemployeeordersnotesections_employee_ord er_id,wrse mployeeordersnotesections_section_id
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 3941822
    Extra: Using where; Using temporary; Using filesort

    Obviously, the performance on the latter is simply unacceptable.

    I've tried analyzing the tables. I've tried rebuilding the indexes. A force index will solve it, but I'd like to get to the underlying issue of why some servers, with identical configurations, are returning different index paths. What is this sort of problem called? Anyone know of any reference material that can help here?

  • #2
    It has to do with the cardinality of the indices. They are always computed (after rebuilding, when analyzing, or when starting MySQL) based on a subset of your rows, not all rows.

    Maybe it works to add some rows to your index wrsemployees_practice_id, but that will depend on your query.


    • #3
      I agree with gmouse. Note that what's changing is not just the index, but the join order. I would suggest using STRAIGHT_JOIN hints instead of FORCE INDEX. Sometimes scanning a big table and doing a "star schema" join is actually much more efficient than what seems to be a bunch of fast index lookups, due to use of sequential I/O instead of random, but in your case I take it this is not desired.


      • #4
        Thank you, I'd never seen STRAIGHT_JOIN, and some initial tests looks like it's working. (One of my biggest problems is that I'm one of those sysadmins that was dragooned into being a DBA without any real training)

        The ordering does seem to be the major issue. It's not a small issue of I/O, it's the difference between seconds and hours. The whole reason this came up is when I started seeing queries running very slow, and one that normally took about five seconds was running for half an hour when I killed it. I've just tried that one with STRAIGHT_JOIN instead of JOIN and it seems to have corrected it.

        I wish there was some way I could correct the underlying way MySQL is deciding between the patths, though, rather than going after individual queries.

        Thanks for your help!


        • #5
          Forbidden wrote on Thu, 13 October 2011 21:47
          I wish there was some way I could correct the underlying way MySQL is deciding between the patths, though, rather than going after individual queries.
          Haven't we all wished for a magic bullet.

          Problem is that the algorithm to choose execution path is a huge compromise between speed (both to sample data and to figure out an execution path), assumptions about your hardware and a lot of different types/combinations of queries.
          Sometimes the wrong path is chosen, but if you would start to tweak the general parameters you would probably end up with a worse compromise.

          My general suggestion when a database server chooses a really bad execution path is that either the query or the existing indexes are not spot on, which means that the optimizer has a hard time to decide on the best path and are more prone to choose a bad one. Which may or may not be true in your case.

          Happy optimizing!