query efficiency
Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? The table is huge, 25M rows or so, but I can create any indexes.
Re: query efficiency
2011/09/27 05:32 -0700, supr_star Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? I cannot answer the real question, but there is this: select name,(SELECT status FROM mytable AS x WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status, min(dt), max(dt),count(*) from mytable group by name I will not vouch that this is more efficient than joining. (Surely there are enough of this that this is entitled to a special SQL construct.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query efficiency
Hi Dan, Sending this to general list too since it's not a Windows specific question. Just run the UPDATE with all the column you want to update. :-) MySQL won't update the ones that haven't changed (which may have been said). Don't waste time trying to determine whether you should include a column in the UPDATE or not. The only time it could be an issue is if you're setting a column to a *REALLY* long value. It will take longer to send the query to the server (especially over a network) and MySQL will take a little more time to parse it. Hope that helps. Matt - Original Message - From: Daniel Canham Sent: Monday, January 12, 2004 2:48 AM Subject: Re: query efficiency Thats not really what I meant. I have 5 (or whatever) columns in a table. I need to update values changed in those columns during processing. But I have no way of knowing what column values need updating. Is it worth my while to write a dirty field checker (value has changed) or doesn't the mysql engine care on UPDATE whether it is UPDATEing 1 or all 10 columns. The way you have it, you are doing all UPDATEs in one statement, or each column on a seperate UPDATE. In that case obviously it is faster to run one update. My question was is it more efficient to execute an single statement UPDATE that has just the changed columns, or a single statement UPDATE that contains all columns every time because the mysql engine treats them the same. -Dan -Original Message- From: robert_rowe [mailto:[EMAIL PROTECTED] Sent: Saturday, January 10, 2004 1:17 PM To: [EMAIL PROTECTED] Subject: [mysql] Re: query efficiency By the way, MySQL won't actually do the update is the existing value is the same as what is already in the fields. Try something like this: pseudocode print now for i=1 to 1 update table1 set col1=i+4, col2=i+3,col3=i+2,col4=i+1,col5=i; next i print now for i=1 to 1 update table1 set col1=i+4 update table1 set col2=i+3 update table1 set col3=i+2 update table1 set col4=i+1 update table1 set col5=i next i /pseudocode I suspect that the first way will be faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query efficiency question
Hi all, I have two tables containing the following: table1: +--++ | UNID | DATE | +--++ | 08294D5D0F9ABE6D46663300BDB6521C | 2001-09-09 02:00:30| | FF4210F70A19D36856663300BDB6521C | NULL | | FB499B109E1C6E143300BDB6521C | NULL | | 9429E5EC91F7508676663300BDB6521C | NULL | | 578313DA1378F96E86663300BDB6521C | 2001-10-21 02:00:40| ... etc etc table2: +-+--+---+--+ | ID | PO_UNID | CONTENT | PO_DOCID | +-+--+---+--+ | 519 | 08294D5D0F9ABE6D46663300BDB6521C | on| D6EBA9F0D5D49280C1256BDB00336664 | +-+--+---+--+ | 520 | 08294D5D0F9ABE6D46663300BDB6521C | off | DFAEFFFWE33771FSDSF428DSF355 | +-+--+---+--+ My problem is that the following query results in several thousand tupels: SELECT UNID FROM TABLE1 WHERE DATE IS NULL; Therefore I use: SELECT UNID FROM TABLE1 WHERE DATE IS NULL LIMIT 1; Now I want to do the following: SELECT UNID FROM TABLE1 WHERE DATE IS NULL LIMIT 1; SELECT CONTENT FROM TABLE2 WHERE PO_UNID='RESULT PREVIOUS QUERY' Instead of writing two queries this could be written as: mysql select 0.UNID, c.CONTENT - from table1 o, table2 c - where o.DATE IS NULL - and o.UNID = c.PO_UNID - LIMIT 1; This results query gives me the right result but it's execution time takes allmost 6 times longer then the two seperate queries. I'm planning to make these queries in a JAVA program (Using the JDBC-bridge). Which variation is smarter? Using the 2 queries, or using the second variation met the join? Any help would be greatly appreciated. Many Thanks, Harm de Laat Informatiefabriek The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL query efficiency - JOINs
I have a question regarding the efficiency of an explicit JOIN statement vs. an implicit one. What I mean: I have a many-to-many mapping through a mapping table. I.e. I have different types of tools mapped to several categories in which they might belong. For instance, a complete programming suite may be categorized both as a debugger, a C-compiler and an assembler... This programming tool is mapped to all these 3 categories through a table just coupling the id's against each other... If the ID of the programming tool itself is 134, the entries for this tool in the mapping table will be like this: +++ | ToolID | TypeID | +++ |134 | 24 | |134 | 19 | |134 | 17 | +++ Now I wonder, when extracting all the info for a certain tool from the database, will it actually be more efficient to use the explicit JOIN statement rather than just tie the keys to one another in the query? This may seem very basic to some; but my formal database knowledge IS very basic. Please, if you can shed some light on the topic or give me som epointers to a good reference; you are very welcome to do so! Best regards, Eivind :-) sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
