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 302433402 by pludi on Tuesday 29th of June 2010 01:03:20 PM
Old 06-29-2010
It's been a while since I've spoken MySQL... but I'd do it this way
Code:
CREATE UNIQUE INDEX IX_uploaded_name_time ON uploaded ( file_name, file_time )

But the ALTER TABLE syntax (the first one) should get you the same result. If not, you can always drop and re-create the index.

It would be interesting tho to see the comparison of the execution timing and EXPLAIN plan with and without the indices.

And if you want to optimize further, here's an article on how to use the MySQL profiler.
This User Gave Thanks to pludi For This Post:
chebarbudo
 

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 LINUX

ndb_print_schema_file

NDB_PRINT_SCHEMA_(1)					       MySQL Database System					      NDB_PRINT_SCHEMA_(1)

NAME

       ndb_print_schema_file - print NDB schema file contents

SYNOPSIS

       ndb_print_schema_file file_name

DESCRIPTION

       ndb_print_schema_file obtains diagnostic information from a cluster schema file.
	       Usage

	   ndb_print_schema_file file_name

       file_name is the name of a cluster schema file. For more information about cluster schema files, see MySQL Cluster Data Node FileSystemDir
       Files[1].

       Like ndb_print_backup_file and ndb_print_sys_file (and unlike most of the other NDB utilities that are intended to be run on a management
       server host or to connect to a management server) ndb_schema_backup_file must be run on a cluster data node, since it accesses the data
       node file system directly. Because it does not make use of the management server, this utility can be used when the management server is
       not running, and even when the cluster has been completely shut down.
	       Additional Options

       None.

COPYRIGHT

       Copyright (C) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

       This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as
       published by the Free Software Foundation; version 2 of the License.

       This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

       You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation,
       Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

NOTES

	1. MySQL Cluster Data Node FileSystemDir Files
	   http://dev.mysql.com/doc/ndbapi/en/ndb-internals-ndbd-filesystemdir-files.html

SEE ALSO

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

AUTHOR

       Oracle Corporation (http://dev.mysql.com/).

MySQL 5.5							    01/30/2014						      NDB_PRINT_SCHEMA_(1)
All times are GMT -4. The time now is 02:44 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy