use Perl

• wrong indexes (Score:2)

  by IlyaM (2933)

  How many rows in table 'invoice' with affid=654248? I guess not many. How many rows in table 'bill' with desctype=0? I guess a lot. If I guessed right you need an index on 'invoice.affid'. Once you add it mysql's query optimizer will switch order in which it does query. Right now it tries to find all records in table 'bill' with desctype=0 and then it joins result with table 'invoice'. This is slooooooooooow because of too many matching records in table 'bill'.

  On second thought you probably should have mu

  --
  

  Ilya Martynov (http://martynov.org/ [martynov.org])
  • Re:wrong indexes (Score:2)

    by Ovid (2709) on 2007.09.04 4:46 (#57402) Homepage Journal

    Why the multi-column index? (if you see my response above, the index on affid was enough to solve this). The multi-column index on affid and commpaid generates the following:

    mysql> explain SELECT authname FROM invoices i WHERE i.affid = 654248 AND i.commpaid = 0;
    +----+-------------+-------+------+--------------------+-------------------- +---------+-------------+------+-------------+
    | id | select_type | table | type | possible_keys      | key                | key_len | ref         | rows | Extra       |
    +----+-------------+-------+------+--------------------+-------------------- +---------+-------------+------+-------------+
    |  1 | SIMPLE      | i     | ref  | idx_invoices_affid | idx_invoices_affid | 10      | const,const |   98 | Using where |
    +----+-------------+-------+------+--------------------+--------------------+ ---------+-------------+------+-------------+

    A single index on just affid generates this:

    mysql> explain SELECT authname FROM invoices i WHERE i.affid = 654248 AND i.commpaid = 0;
    +----+-------------+-------+------+--------------------+-------------------- +---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys      | key                | key_len | ref   | rows | Extra       |
    +----+-------------+-------+------+--------------------+-------------------- +---------+-------+------+-------------+
    |  1 | SIMPLE      | i     | ref  | idx_invoices_affid | idx_invoices_affid | 5       | const |   15 | Using where |
    +----+-------------+-------+------+--------------------+--------------------+ ---------+-------+------+-------------+

    The single column index scans fewer rows (not sure why) and reduces the query to .15 seconds from the .30 with the multi-column indexes. Either, of course, is a vast improvement :)

    Reply to This

    Parent

    • Re: (Score:2)

      by IlyaM (2933)

      What is the set of possible values for column i.commpaid? If it is a small set (say, it is boolean field which can be set only to 0 or 1) then using index on this column can be slower then just direct scan of rows one by one. I'm not sure why number of rows in explain select is higher though. If I recall correctly this number is just an estimate so could be that mysql gets it slightly wrong for some reason but I'm not sure about this.

      --
      

      Ilya Martynov (http://martynov.org/ [martynov.org])
      • Re: (Score:2)

        by Ovid (2709)

        It is a boolean value, so just zero or one. And yes, I was researching 'rows' in the past as a means of easily identifying after the fact whether or not a table had records added or deleted and I recall that this value is indeed not always completely accurate. Sometimes merely altering data seemed to change that value.
    • Re: (Score:1)

      by ruz (8128)

      Output of EXPLAIN is provided by planner/optimizer, so it has nothing to do with real number of rows matching conditions. MySQL's optimizer does several random lookups in indexes to estimate number of rows.