mysql select query optimization.. Post: 302496066

Login or Register to Ask a Question and Join Our Community

Sponsored Content

Top Forums Shell Programming and Scripting mysql select query optimization.. Post 302496066 by senkerth on Saturday 12th of February 2011 03:25:02 AM

Old

02-12-2011

Registered User

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 DESC SEPARATOR '|_|'),'|_|',3),
timediff(av.AUC_END_TIM,time(now())) as RemTime,
av.AUC_END_TIM,av.AUC_RSV_PRC,av.AUC_DATE,av.AUC_STATUS
from auc_hist_tbl as ah, aucveh_sched_tbl as av
where ah.AUC_UNI_NO = ''
AND av.AUC_UNI_NO = ''

auc_hist_tbl has 15 lac records ... i have indexed AUC_UNI_NO ..
but still its taking time ..
can u guide where's my query going wrong..
thanks ..

Moderator's Comments:

Mod Comment

Double post. Continue here.

Last edited by Scott; 02-12-2011 at 05:03 AM..

senkerth

View Public Profile for senkerth

Find all posts by senkerth

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need Help (Select query)

Dear All, This may sound a simple query but a non technical person like me is not able to do it, So please help me out. I m using Unix... isql I jst wanted to do something like following select * from xyz where ID= xxxxxxxx (8 digit ID) Here if i put single 8 digit ID then the...

2. Programming

Select several minimum values from row (MySQL)

Hello there. I've got the query like that SELECT count(tour_id) AS cnt FROM orders JOIN tours ON orders.tour_id=tours.id GROUP BY tour_id The result Is cnt 1 4 2 1 1 Now i have to select all records with minimum values in field "cnt" MySQL function min() returns only one....

3. Shell Programming and Scripting

Select query implement in a shell

I have been asked to create a shell script that accepts a number of SQL select queries as input, runs them in sequence, spools the output to an EXCEL workbook, where, each worksheet is an output of a Select statement run above. The workbook should be in a .XLS format. If a particular select...

4. Shell Programming and Scripting

Select record with MAX value in MySQL

Hi there, I have trouble selecting record that contain one biggest value for a group of other values. I mean, this is my table: mysql> SELECT * FROM b; +----+------+-------+ | id | user | value | +----+------+-------+ | 1 | 1 | 100 | | 3 | 1 | 150 | | 5 | 1 | 300 | | 6...

5. Shell Programming and Scripting

mysql how to select a specific row from a table

i have a table records ------------ id | user | time | event 91 admin | 12:00 | hi 92 admin | 11:00 | hi 93 admin | 12:00 | bye 94 admin | 13:00 | bye 95 root | 12:00 | hi 96 root | 12:30 | hi 97 root | 12:56 | hi how could i only select and display only the user and event from...

6. 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...

7. UNIX and Linux Applications

MySQL optimization or why the server is worsened

Hello Please advise me how can I optimize my MySQL server. Or advise which way to look. Maybe someone had similar problems? Over the past two weeks the MySQL server dropped 2 times. I began searching for ways to optimize the server. Overall the picture like that: There is a separate...

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...

LEARN ABOUT CENTOS

rpmspec

RPMSPEC(8)						      System Manager's Manual							RPMSPEC(8)

NAME

       rpmspec - RPM Spec Tool

SYNOPSIS

   QUERYING SPEC FILES:
       rpmspec {-q|--query} [select-options] [query-options] SPEC_FILE ...

   select-options
	[--rpms]
	[--srpms]

   query-options
	[--qf,--queryformat QUERYFMT]
	[--target TARGET_PLATFORM]

   QUERY OPTIONS
       The general form of an rpm spec query command is

       rpm {-q|--query} [select-options] [query-options]

       You may specify the format that the information should be printed in. To do this, you use the

	--qf|--queryformat QUERYFMT

       option, followed by the QUERYFMT format string.	See rpm(8) for details.

   SELECT OPTIONS
	--rpms Operate on the binary package header(s).
	--srpm Operate on the source package header(s).

SEE ALSO

       popt(3),
       rpm(8),
       rpmdb(8),
       rpmkeys(8),
       rpmsign(8),
       rpm2cpio(8),
       rpmbuild(8),

       rpmspec	--help - as rpm supports customizing the options via popt aliases it's impossible to guarantee that what's described in the manual
       matches what's available.

       http://www.rpm.org/ <URL:http://www.rpm.org/>

AUTHORS

       Marc Ewing <marc@redhat.com>
       Jeff Johnson <jbj@redhat.com>
       Erik Troan <ewt@redhat.com>
       Panu Matilainen <pmatilai@redhat.com>

Red Hat, Inc							  29 October 2010							RPMSPEC(8)