Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Desperately Need MySQL Query Optimization Help 15 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • How fast is

    SELECT authname FROM invoices i WHERE i.affid = 654248 AND i.commpaid = 0;

    You're doing a full table scan on invoices alone, without the join, and that SHOULD be painful.

    So, index on affid.

    • Bingo! That reduces the query time down to .15 seconds :)

      I don't understand the EXPLAIN output as well as I should (duh!). How did you deduce the full table scan on invoice from this?

      +----+-------------+-------+--------+------------------+----------+---------+--- -------------+---------+-------------+
      | id | select_type | table | type   | possible_keys    | key      | key_len | ref            | rows    | Extra   

      • Don't look at me, I try to avoid mysql :-)
      • Well, it is not really a full table scan but it is close to this. Explain select says that it needs to scan 1879054 rows out of 3770990. Concerning performance it is pretty much equivalent to the full table scan. Actually if lookup on an index returns ~50% or more of your table it could be even slower that the full table scan.
        --

        Ilya Martynov (http://martynov.org/ [martynov.org])

        • Oh, I forgot to add. It is a full table scan on table 'bill', no on table 'invoice'.
          --

          Ilya Martynov (http://martynov.org/ [martynov.org])

          • Without knowing anything about mysqls optimizer, that's easy enough to understand.

            With the original setup, there was a key (and therefore likely an index) on b.desctype, limiting the need for a full tablescan on bills down to "just" half the table. Which turns out to be slightly less work than a full table scan on invoices. So now it has a (long) list of invoice id's it can use the primary key index on invoices to find.

            Adding the index on affid, makes it a LOT cheaper to use that than either the full or

        • Um, your 50% figure is off slightly.

          By about an order of magnitude.

          The actual figure is highly hardware dependent, but should generally be in the range 5-10%.

          Cheers,
          Ben

  • 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])

    • 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           

      • 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])

        • 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.

      • 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.
  • SELECT cost_net,b.authname AS b_authname, i.authname AS i_authname FROM bill b INNER JOIN invoices i USING(invoice) WHERE i.affid = 654248 AND b.desctype = 0 AND i.commpaid = 0; Now, i think you probably want to have invoice indexed on both sides of the equation, BUT since mysql can only use one index per table I suggest you have an index on i (invoice, affid) and b (invoice) I don't know how your tables look with data in them or your other queries, but you should probably have less indexes and more index
  • SELECT cost_net,b.authname AS b_authname, i.authname AS i_authname
    FROM bill b INNER JOIN invoices i USING(invoice)
    WHERE i.affid = 654248 AND b.desctype = 0 AND i.commpaid = 0;

    Now, i think you probably want to have invoice indexed on both sides of the equation, BUT since mysql can only use one index per table I suggest you have an index on i (invoice, affid) and b (invoice)

    I don't know how your tables look with data in them or your other queries, but you should probably have less indexes and more indexes tha
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.