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.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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           

      • by ruz (8128) on 2007.09.04 18:31 (#57447)
        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.