Seems the MySQL performance forums don't get huge amounts of traffic. Can anyone recommend a good forum where I can start asking MySQL tuning questions and actually have a chance of getting them answered?
If you're curious, my current dilemma involves a 50 second response time on a simple join. I've run both OPTIMIZE and ANALYZE over the tables in the query and after about an hour and a quarter, the index I list in that post was finally created. However, it had no affect on query performance and the only difference in the EXPLAIN is that it listed the index as another possible key but didn't use it.
In short, I'm stuck. I don't know what to do next and I have no idea how that index can be applied to our production database without bringing the box to its knees (I have no admin skills). One person said "just don't use joins" and I might have to for this simple query, but not being able to use joins in SQL means I might as well fall back to a flat file database -- something I honestly feel I could optimize easier than our current MySQL setup :(
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.
Re:join?
Ovid on 2007-09-04T09:43:16
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 |
+----+-------------+-------+--------+------------------+----------+--------- +----------------+---------+-------------+
| 1 | SIMPLE | b | ref | desctype,invoice | desctype | 4 | const | 1879054 | |
| 1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 4 | data.b.invoice | 1 | Using where |
+----+-------------+-------+--------+------------------+----------+---------+ ----------------+---------+-------------+The reason I thought it was pretty much a problem on the bill table is because I have almost 2 million records with a desctype of zero and I figured MySQL was scanning those linearly.
Re:join?
lbr on 2007-09-04T09:46:17
Don't look at me, I try to avoid mysql:-) Re:join?
IlyaM on 2007-09-04T10:17:30
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.Re:join?
IlyaM on 2007-09-04T10:24:25
Oh, I forgot to add. It is a full table scan on table 'bill', no on table 'invoice'.Re:join?
lbr on 2007-09-04T10:39:04
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 half table scans.
Re:join?
btilly on 2007-09-05T00:18:59
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
On second thought you probably should have multicolumn index (i.affid, i.commpaid). In this case whole part 'i.affid = 654248 AND i.commpaid = 0' can be found using this index.
Re:wrong indexes
Ovid on 2007-09-04T09:46:34
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 :) Re:wrong indexes
IlyaM on 2007-09-04T10:05:36
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.Re:wrong indexes
Ovid on 2007-09-04T10:42:42
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:wrong indexes
ruz on 2007-09-04T23:31:40
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.
Re:try this one
groditi on 2007-09-04T17:25:49
ugh sorry about that. look below.