Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Select record with MAX value in MySQL
Top Forums Shell Programming and Scripting Select record with MAX value in MySQL Post 302433719 by chebarbudo on Wednesday 30th of June 2010 10:26:31 AM
Old 06-30-2010
Time is indeed an issue given the fast growing number of uploaded files.
I isolated one operation: retrieve the file id on 300,000 records.
With a massive update, it takes about 75 seconds.
With a one by one update, I started clocking more than an hour ago and it's not even finished.

Anyway, thank you very much for helping me go deeper in the structure of my database. It was very interesting.

Cheers
Santiago

---------- Post updated at 15:11 ---------- Previous update was at 15:06 ----------

Usually there's a way to reward people who help with bits.
I can't find the button anymore. Do you know where it is?

---------- Post updated at 15:24 ---------- Previous update was at 15:11 ----------

I stopped the query because I needed my CPU ;o)
Trying to extrapolate the number of records already processed, I evaluated that the one by one update is around 2,000 times longer than the massive update.

---------- Post updated at 15:26 ---------- Previous update was at 15:24 ----------

I forgot to mention that I think the incredible difference is certainly due to the well chosen indexes you told me to create.
 

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Select Record based on First Column

Hi, I have a file with multiple records...and I have to select records based on first column....here is the sample file... I01,abc,125,1a2,LBVI02 I01,abc,126,2b5,LBVI02 I02,20070530,254,abc,LLBI01 I02,20070820,111,bvd,NGBI01 I need all records with I01 in first field in one file and... (8 Replies)
Discussion started by: mgirinath
8 Replies

2. Shell Programming and Scripting

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (0 Replies)
Discussion started by: senkerth
0 Replies

3. UNIX for Advanced & Expert Users

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (1 Reply)
Discussion started by: senkerth
1 Replies

4. UNIX for Dummies Questions & Answers

Select max value based on filename

Hi, I would just like to know how to get the file with the max filename on a directory and get rid of all the others. For example, in directory A:/ i have the ff files: APPLE2001 APPLE2002 APPLE2003 GRAPE2004 what I want to get is the max in files whose filenames start with APPLE*,... (4 Replies)
Discussion started by: madden
4 Replies

5. Shell Programming and Scripting

Max value Record from a File

I have a file with 3 columns I want to get the record with the max salary. If there are more than 1 record having the highest salary, i want any one of them. empid,ename,Sal 1,abc,100 2,def,200 3,xyz,300 4,pqr,100 5,mnq,300 Output Any record with 300 as salary Please use next... (1 Reply)
Discussion started by: traininfa
1 Replies

6. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

7. Shell Programming and Scripting

Select record having different value in second column

I want records which have more than one and different value in the second column on the below sample file. Ex, I have the samle file below :- XYZ 1 XYZ 3 abc 1 abc 1 qwe 2 qwe 1 qwe 3 I want to select XYZ and QWE line only. (6 Replies)
Discussion started by: Sanjeev Yadav
6 Replies

8. Programming

MySQL select user with the same IP address

Dear community, I woul like to make a query to output all the users having the same IP address. The table is somethig like: name logged_ip ==== ========= user1 127.0.0.1 user2 127.0.0.2 user3 127.0.0.3 user4 127.0.0.1 user5 127.0.0.2 user6 127.0.0.5I used this query... (4 Replies)
Discussion started by: Lord Spectre
4 Replies

9. Shell Programming and Scripting

Script to record max memory usage of program

Hello, I am working on an application that uses allot of memory depending on the input. I am also working on more than one processing algorithm. The program has a long processing time (hours) so it would be nice to be able to monitor the maximum memory footprint of the application during runs... (1 Reply)
Discussion started by: LMHmedchem
1 Replies

LEARN ABOUT OPENSOLARIS

mysqlshow

mysqlshow(1)							  MySQL database						      mysqlshow(1)

NAME

       mysqlshow
	- Shows the structure of a mysql database (databases,tables and columns)

USAGE

       shell> mysqlshow [OPTIONS] [database [table [column]]]

SYNOPSIS

       mysqlshow   [-#|--debug=...]    [-?|--help]   [-c|--character-sets-dir=...]   [-C|--compress]  [-h|--host=...]	[-i|--status]  [-k|--keys]
       [-p|--password[=...]]  [-P|--port=...]  [-S|--socket=...]  [-u|--user=#] [-V|--version]

DESCRIPTION

       -#|--debug=...
	      output debug log. Often this is 'd:t:o,filename`

       -?|--help
	      display help and exit

       -c|--character-sets-dir=...
	      Directory where character sets are

       -C|--compress
	      Use compression in server/client protocol

       -h|--host=...
	      connect to host

       -i|--status
	      Shows a lot of extra information about each table

       -k|--keys
	      show keys for table

       -p|--password[=...]
	      password to use when connecting to server If password is not given it's asked from the tty.

       -P|--port=...
	      Port number to use for connection

       -S|--socket=...
	      Socket file to use for connection

       -u|--user=#
	      user for login if not current user

       -V|--version
	      output version information and exit

NOTE

       If last argument contains a shell or SQL wildcard (*,?,% or _) then only what's matched by the wildcard is shown.  If no database is  given
       then  all matching databases are shown.	If no table is given then all matching tables in database are shown If no column is given then all
       matching columns and columntypes in table are shown

SEE ALSO

       isamchk(1), isamlog(1), mysql(1), mysqlaccess(1), mysqladmin(1), mysqld(1), mysqld_multi(1), mysqld_safe(1), mysqldump(1), mysql_fix_privi-
       lege_tables(1), mysql_zap(1), perror(1), replace(1)

       For  more information please refer to the MySQL reference manual, which may already be installed locally and which is also available online
       at http://www.mysql.com/doc/en/

BUGS

       Please refer to http://bugs.mysql.com/ to report bugs.

AUTHOR

       Ver 1.0, distribution 4.0.24 Michael (Monty) Widenius (monty@mysql.com), MySQL AB (http://www.mysql.com/).  This  software  comes  with	no
       warranty.   Manual page by L. (Kill-9) Pedersen (kill-9@kill-9.dk), Mercurmedia Data Model Architect / system developer (http://www.mercur-
       media.com)

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +--------------------+------------------------------------+
       |  ATTRIBUTE TYPE    |	       ATTRIBUTE VALUE		 |
       +--------------------+------------------------------------+
       |Availability	    | SUNWmysqlr, SUNWmysqlu, SUNWmysqlt |
       +--------------------+------------------------------------+
       |Interface Stability | External				 |
       +--------------------+------------------------------------+
NOTES

       Source for mysql is available on http://opensolaris.org.

MySQL 4.0							 19 December 2000						      mysqlshow(1)
All times are GMT -4. The time now is 01:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy