Re: Optimizer Bug?
On Oct 25, 2006, at 5:32 PM, Dan Buettner wrote: My understanding of what is happening here is this: The 'rows' column of EXPLAIN output is an estimate of how many rows MySQL thinks it will likely have to examine in a table to get your answer. When there's an index, it will hopefully be able to use that to exmaine a small subset of the rows in the table. Problem here is, MySQL thinks it will have to examine 1463 of 1950 rows. At that point (or any point higher than about 30%) MySQL will decide that a table scan may be faster. Hence the decision to not use the d_id index. When you drop the index, MySQL can no longer plan to eliminate any rows using an index, so it knows up front it will have to do a table scan, giving you the 1950 answer for the table with no d_id index. What's probably happening is that you have a large grouping of the 5098 number in your data, based on a quick read of your query. Make sense? I guess that makes sense. It's not very obvious, and arguably wrong, that the type and rows columns in the EXPLAIN output are not necessarily referring to the same scenario. Apparently, type always refers to what /will/ happen, and rows refers to how many rows /might/ be looked at. Thanks. -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Optimizer Bug?
On Oct 27, 2006, at 11:50 AM, Dan Nelson wrote: MySQL is just giving you as much information as it can without actually running the query. It knows how it will go about running the query (so type is known absolutely), but it doesn't know exactly what it will get (so rows is only a guess). Nothing wrong with that. If type is known absolutely, and is ALL, as it was in this case, why would EXPLAIN ever report a rows value less than the number of rows in the table ( as it did here )? At risk of sounding too much like Bill Clinton, what exactly does ALL mean, then? -- David Hillman LiveText, Inc 1.866.LiveText x235
Optimizer Bug?
All;
Am I crazy, or doesn't this have to be an optimizer/explain bug?
SQL interspersed with comments follow...
mysql CREATE TEMPORARY TABLE `table_a` ( `s_id` int(11) NOT NULL
default '0', `r_id` int(11) NOT NULL default '0', `d_id` int
(11) NOT NULL default '0', `status` enum('open','close') NOT NULL
default 'open', key `s_id` ( `s_id` ), key `d_id` ( `d_id` ) )
SELECT MAX(fs.s_id) as s_id, fs.r_id, fs.d_id, fs.status FROM
table_c AS fs WHERE fs.d_id=5098 AND fs.status='close' GROUP BY
fs.r_id;
Query OK, 1950 rows affected (0.03 sec)
Records: 1950 Duplicates: 0 Warnings: 0
We created a temp table, and stuck 1950 rows in it.
mysql explain SELECT fs.s_id, fs.r_id, fst.* FROM table_a
AS fs LEFT OUTER JOIN table_b AS fst ON
fs.s_id=fst.s_id LEFT OUTER JOIN table_d AS ff ON
ff.f_id=fst.f_id WHERE fs.d_id='5098' AND ff.status='active';
++-+---++
+---+-+-+--
+-+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
++-+---++
+---+-+-+--
+-+
| 1 | SIMPLE | fs| ALL| d_id | NULL |
NULL | NULL| 1463 | Using where |
| 1 | SIMPLE | fst | ref| s_id | s_id | 4 |
database.fs.s_id |1 | |
| 1 | SIMPLE | ff| eq_ref | PRIMARY,status |
PRIMARY | 4 | database.fst.f_id |1 | Using where |
++-+---++
+---+-+-+--
+-+
3 rows in set (0.00 sec)
Now when we join on that temporary table_a ( aka fs ), there's
two interesting things happening. One, there's a possible_key called
d_id, but it's not using it. Two, it's allegedly an ALL join, but
only showing 1463 of the 1950 rows.
mysql alter table table_a drop index d_id;
Query OK, 1950 rows affected (0.01 sec)
Records: 1950 Duplicates: 0 Warnings: 0
Now we drop the possible_key that it wasn't using anyway,
and... run the same explain...
mysql explain SELECT fs.s_id, fs.r_id, fst.* FROM table_a
AS fs LEFT OUTER JOIN table_b AS fst ON
fs.s_id=fst.s_id LEFT OUTER JOIN table_d AS ff ON
ff.f_id=fst.f_id WHERE fs.d_id='5098' AND ff.status='active';
++-+---++
+---+-+-+--
+-+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
++-+---++
+---+-+-+--
+-+
| 1 | SIMPLE | fs| ALL| NULL |
NULL |NULL | NULL| 1950 | Using where |
| 1 | SIMPLE | fst | ref| s_id | s_id | 4 |
database.fs.s_id |1 | |
| 1 | SIMPLE | ff| eq_ref | PRIMARY,status |
PRIMARY | 4 | database.fst.f_id |1 | Using where |
++-+---++
+---+-+-+--
+-+
3 rows in set (0.00 sec)
Now that join is still an ALL, but it's looking at more rows?
How can it look at more than ALL rows? Why does dropping an index
that it wasn't using change, well, anything? Can someone make some
sense of this for me?
This is on version 4.1.18. Thanks.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
Re: First View
On Aug 24, 2006, at 6:13 PM, Daniel Kasak wrote: That's just how software develops. People start with the later- versioned product, with full features and zero bugs, and progressively remove features and add bugs, while decreasing the version number. I've looked all over the web, and found nothing, so hopefully someone here can help... where can I download MySQL 10 from? ;) -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Row count discrepancy when converting from MyISAM to InnoDB
On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Slave Replication issues
On Jul 25, 2006, at 12:47 PM, David Nance wrote:
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have
seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query
'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date,
end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00',
'-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
One of the indexes on your 'permissions' table in defined to be
'unique', and your application is trying to insert a row with a
duplicate value. This doesn't seem like a replication error, unless
there really isn't a unique index on permissions... but I bet there is.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
Re: 'on duplicate key update' and 'last_insert_id'
On Jun 30, 2006, at 10:44 AM, Rob Desbois wrote: That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly helpful as you have to provide a column to update - however I can just say e.g. ON DUPLICATE KEY UPDATE id=id The problem with this is that if I then do SELECT LAST_INSERT_ID (); then I don't get the id of the 'updated' table, I get the *next* auto increment value. Is the last bit a bug? Yes, http://bugs.mysql.com/bug.php?id=19243 -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Backups with MySQL/InnoDB
On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Timeseries
On Apr 28, 2006, at 7:12 PM, David Lindelöf wrote: Is there an easy way to get the last timestamped record not later than a given date/time? Well, yeah, that part is easy. select * from your_table where timestamp target_time order by timestamp desc limit 1 Your problem seems to be how to get multiple last-timestamped records, and I don't think that answer will be elegant. -- David Hillman LiveText, Inc 1.866.LiveText x235
Two fast queries combined = slow?
This makes no sense to me, please explain if you can. I have two queries, both run in under a tenth of a second. But, when one is run as a sub-query of the other, run time is essentially infinite ( I haven't had that much patience, yet ). For example, although these are not the actual queries; Query A = SELECT id FROM table WHERE key 10 Query B = SELECT row FROM other-table WHERE id IN ( 0,1,2,3,4,5,6,7,8,9 ) Each query runs and returns almost immediately, separately ( and A returns 0-9 ). Query C = SELECT row FROM other-table WHERE id IN ( Query A ) That one goes away and never comes back, as far as I know. How can query C take forever, when composed of two such fast queries? What is done differently when it's run as sub-query, other than a copy to tmp table? Speaking copy to tmp table is the state that query C stays in forever, even when it's only supposed to be copying 10 tiny integers. All the appropriate indices are in place, and explain claims to be using them correctly. There are a few million rows in the respective tables, but it still doesn't add up. What's going on? Thanks. ( MySQL 4.1.14 on various systems ) -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Two fast queries combined = slow?
You are the man, Joe. The wall next to my desk thanks you, for stopping me from continuing to beat my head against it. -- David Hillman LiveText, Inc 1.866.LiveText x235 On Nov 22, 2005, at 7:50 PM, Joseph Cochran wrote: Subqueries in 1.4.14 don't use indexes, instead performing full- table scans. Basically, they're broken in that version of MySQL. -- Joe
