Complex SQL optimization vs. general-purpose language
Any SQL rewriting gurus know how I might be able to optimize this
query? The schema:
mysql> show columns from transactionlog;
+---+---+--+-+-++
| Field | Type | Null |
Key | Default | Extra |
+---+---+--+-+-++
| id| int(11) | NO |
PRI | NULL| auto_increment |
| transactionid | varchar(10) | NO |
MUL | NULL||
| queryid | tinyint(4)| NO |
| NULL||
| tableid | varchar(30) | NO |
MUL | NULL||
| tupleid | int(11) | NO |
| NULL||
| querytype | enum('select','insert','delete','update') | NO |
| NULL||
| schemaname| varchar(20) | YES |
| NULL||
| partition | tinyint(3) unsigned | YES |
| NULL||
+---+---+--+-+-++
8 rows in set (0.04 sec)
The query:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(distinct transactionid)
from transactionlog
where transactionid in (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
(t.tableid, t.tupleid, 'update')
group by transactionid
having count(*) > 0
)
) weight
from transactionlog t
group by tableid, tupleid
having weight > 0 and count(*) > 1
) u;
This is the output of EXPLAIN and mk-visual-explain:
++++---+---+---+-+---+--+--
+
| id | select_type| table | type | possible_keys |
key | key_len | ref | rows | Extra
|
++++---+---+---+-+---+--+--+
| 1 | PRIMARY| | ALL | NULL |
NULL | NULL| NULL | 13 |
|
| 2 | DERIVED| t | ALL | NULL |
NULL | NULL| NULL | 68 | Using filesort
|
| 3 | DEPENDENT SUBQUERY | transactionlog | index | NULL |
transactionid | 12 | NULL | 68 | Using where; Using index
|
| 4 | DEPENDENT SUBQUERY | transactionlog | ref | tableid |
tableid | 36 | func,func |2 | Using where; Using
temporary; Using filesort |
++++---+---+---+-+---+--+--+
Table scan
rows 13
+- DERIVED
table derived(t,transactionlog,temporary(transactionlog))
+- DEPENDENT SUBQUERY
+- DEPENDENT SUBQUERY
| +- Filesort
| | +- TEMPORARY
| | table temporary(transactionlog)
| | +- Filter with WHERE
| |+- Bookmark lookup
| | +- Table
| | | table transactionlog
| | | possible_keys tableid
| | +- Index lookup
| | keytransactionlog->tableid
| | possible_keys tableid
| | key_len36
| | reffunc,func
| | rows 2
| +- Filter with WHERE
| +- Index scan
|keytransactionlog->transactionid
|key_len12
|rows 68
+- Filesort
+- Table scan
rows 68
+- Table
table t
That is a lot of work. I can write the equivalent logic in Python
while making a single pass:
results = query("""
select tableid, tupleid, transactionid, id, querytype
from transactionlog_2warehouse
""")
_tab, _tup = None
ids = []
weight = 0
saw_upd = False
for tab, tup, txn, id, qt in results:
if (_tab, _tup) != (tab, tup):
if len(ids) > 1 and weight > 0:
print weight, ids
weight = 0
ids = []
_txn = None
if _txn != txn:
saw_upd = False
if qt == 'update' and not saw_upd:
weight += 1
saw_upd = True
ids += [id]
Is it possible to achieve the Python single-pass performance using
pure SQL? Thanks in advance!
--
Yang Zhang
http://www.mit.edu/~
Subquery scoping
I have the following query: select concat(weight, ' ', ids, '\n') from ( select tableid, tupleid, group_concat(id separator ' ') as ids, ( select count(*) from ( select transactionid from transactionlog where (tableid, tupleid, querytype) = (t.tableid, t.tupleid, 'update') group by transactionid having count(*) > 0 ) v ) weight from transactionlog t group by tableid, tupleid having weight > 0 and count(*) > 1 ) u; However, mysql complains about the reference to t from the innermost query: ERROR 1054 (42S22): Unknown column 't.tableid' in 'where clause' Why is this an error? Is this a bug? The MySQL docs on scoping rules don't say anything about this. I was able to suppress the error with this hack rewrite: select concat(weight, ' ', ids, '\n') from ( select tableid, tupleid, group_concat(id separator ' ') as ids, ( select count(distinct transactionid) from transactionlog where transactionid in ( select transactionid from transactionlog where (tableid, tupleid, querytype) = (t.tableid, t.tupleid, 'update') group by transactionid having count(*) > 0 ) ) weight from transactionlog t group by tableid, tupleid having weight > 0 and count(*) > 1 ) u; I'm not sure if this creates an additional unnecessary join, though -- trying to make sense of the output of EXPLAIN has been a separate exercise in frustration all to itself (even with mk-visual-explain). Thanks in advance for any answers. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting Dates
On Jan 31, 2010, at 7:35 PM, ML wrote: > Hi All, > > Switching from Oracle to MySQL, I seem to be having some difficulty selecting > dates using between or even where >= and <= like: > > SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' > ORDER BY order_date; No "=" after BETWEEN. > > or > > SELECT * FROM orders WHERE order_date =>'2010-01-01' AND <= '2010-01-30' > ORDER BY order_date; Need "order_date <=", not just "<=". > > Neither of these work. > > What am I missing? > > -ML -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help about the charset
On Wed, Feb 3, 2010 at 6:33 PM, Thiyaghu CK wrote: > Hi Moli, > > I hope this is because there is spelling mistake. It should be > '--with-extra-charsets=all', but you have given > '--with-extral-charsets=all'. That's why only the utf8 has been installed > and not the other charsets. > Thanks a lot Thiyaghu. I really inputed the wrong compiling arguments by mistake. Now I adjust it and recompile mysql, all goes fine. Thanks. -- moli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help about the charset
Hi Moli, I hope this is because there is spelling mistake. It should be * '--with-extra-charsets=all'*, but you have given '--with-*extral*-charsets=all'. That's why only the utf8 has been installed and not the other charsets. Regards, Thiyaghu CK www.mafiree.com On Wed, Feb 3, 2010 at 2:26 PM, wrote: > Hello, > > I have a strange problem on the charset on mysql. > I'm using mysql-5.0.45, compiled from the source, and enabled: > '--with-charset=utf8' '--with-extral-charsets=all' > when compiling it. > > My default charset in my.cnf is utf8: > character-set-server=utf8 > > OK I logined into mysql and run: > > mysql> set names 'gbk'; > ERROR 1115 (42000): Unknown character set: 'gbk' > > The error shows as above. > > These were the 'show' command's output: > > mysql> show variables like 'character%'; > +--++ > | Variable_name| Value | > +--++ > | character_set_client | utf8 | > | character_set_connection | utf8 | > | character_set_database | utf8 | > | character_set_filesystem | binary | > | character_set_results| utf8 | > | character_set_server | utf8 | > | character_set_system | utf8 | > | character_sets_dir | /var/mysql5.0.45/share/mysql/charsets/ | > +--++ > 8 rows in set (0.00 sec) > > mysql> show charset like 'utf8'; > +-+---+---++ > | Charset | Description | Default collation | Maxlen | > +-+---+---++ > | utf8| UTF-8 Unicode | utf8_general_ci | 3 | > +-+---+---++ > 1 row in set (0.01 sec) > > mysql> show charset like 'gbk'; > Empty set (0.00 sec) > > > Since I have enabled --with-extral-charsets=all why can't I use gbk > charset? > How to resolve it? > > Thanks. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=theyaho...@gmail.com > >
help about the charset
Hello, I have a strange problem on the charset on mysql. I'm using mysql-5.0.45, compiled from the source, and enabled: '--with-charset=utf8' '--with-extral-charsets=all' when compiling it. My default charset in my.cnf is utf8: character-set-server=utf8 OK I logined into mysql and run: mysql> set names 'gbk'; ERROR 1115 (42000): Unknown character set: 'gbk' The error shows as above. These were the 'show' command's output: mysql> show variables like 'character%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /var/mysql5.0.45/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql> show charset like 'utf8'; +-+---+---++ | Charset | Description | Default collation | Maxlen | +-+---+---++ | utf8| UTF-8 Unicode | utf8_general_ci | 3 | +-+---+---++ 1 row in set (0.01 sec) mysql> show charset like 'gbk'; Empty set (0.00 sec) Since I have enabled --with-extral-charsets=all why can't I use gbk charset? How to resolve it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
