Query Optimization

Hi All

The Query below took around 175 Secs to return 22 Rows..Any way this query
can be optimized

SELECT
Distinct(C.Company_Name) ,U.UserID , IF(U.CompanyID IS
NULL , NULL ,C.Company_Name) as Company_Name,
U.COMPANYID, U.PASSWORD,
U.INDIVIDUAL_COMPANY_NAME,
U.USER_NAME,U.FIRST_NAME,
U.LAST_NAME, U.MIDDLE_NAME,
U.EMAIL,U.PROMOTION_CODE,U.TITLE, U.DESIGNATION,
U.USER_STATUS, U.USER_APP_STATUS,
U.PHONE,U.ADDRESS, U.CREATED_ON,U.FAX, U.ZIP,
U.DEPARTMENT, U.MODIFIED_ON,
U.CITY,U.STATE, U.INDUSTRYID, U.COUNTRYID,
U.CERTIFICATIONID,U.TECHNOLOGY_AREAID,
U.HORIZONTAL_STANDARDS_EXP,
U.ROLEID,U.ROLE_OTHER, U.YEARS_OF_EXP,
U.OTHER_CERTIFICATION2,
U.PROF_MEMBERSHIP1, U.OTHER_CERTIFICATION3,
U.PROF_MEMBERSHIP2,U.OTHER_CERTIFICATION1,
U.HOURLY_RATE,
U.PROF_MEMBERSHIP3,U.WORK_PREFERENCE,
U.OTHER_CERTIFICATION4,
U.ADDITIONAL_COMMENTS,U.WELCOME_MESSAGE,
I.Industry_Name,T.Technology_Area, R.Role
FROM
User_Type_Details UTD,
User_Type_Details B , Users U ,TempGeneralSearch1 T1,Users A
LEFT JOIN Company C on (U.CompanyID = C.CompanyID)
LEFT JOIN Expert_Info EI on (U.UserID = EI.UserID)
LEFT JOIN User_Horizontal_Market UHM on (U.UserID =
UHM.UserID)
LEFT JOIN Industry I on (U.IndustryID = I.IndustryID)
LEFT JOIN Technology_Area T on (U.Technology_AreaID =
T.Technology_AreaID)
LEFT JOIN Role R on (U.RoleID = R.RoleID )
WHERE
U.UserID = UTD.UserID
AND U.User_Status='ACT' AND U.User_App_Status='APP'
AND UTD.User_TypeID IN (4,0,2,3) AND T1.TempID=117



------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Optimization

Pada Mon, 8 Jul 2002 16:07:04 +0530
"Arul" menulis :

> Hi All
>
> The Query below took around 175 Secs to return 22 Rows..Any way this query
> can be optimized

try to use group by instead of distinct .... i usually found group by is faster ;-)

anyway ... performance of query is more related to :
- table definition
- indexing
- mysql parameter values
- OS and hardware spec/limitation

--
Let's call it an accidental feature.
-- Larry Wall

MySQL 3.23.51 : up 17 days, Queries : 365.147 per second (avg).

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Optimization

Hi

I Found Group By to be a bit faster...but still it takes 30 secs to execute
a query...
What could be done..I have Indexing on almost all the tables..

-Arul
----- Original Message -----
From: "Dicky Wahyu Purnomo"
To: "Arul"
Cc:
Sent: Monday, July 08, 2002 4:25 PM
Subject: Re: Query Optimization


> Pada Mon, 8 Jul 2002 16:07:04 +0530
> "Arul" menulis :
>
> > Hi All
> >
> > The Query below took around 175 Secs to return 22 Rows..Any way this
query
> > can be optimized
>
> try to use group by instead of distinct .... i usually found group by is
faster ;-)
>
> anyway ... performance of query is more related to :
> - table definition
> - indexing
> - mysql parameter values
> - OS and hardware spec/limitation
>
> --
> Let's call it an accidental feature.
> -- Larry Wall
>
> MySQL 3.23.51 : up 17 days, Queries : 365.147 per second (avg).
>


------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Optimization

On Monday, 8. July 2002 12:37, Arul wrote:
> Hi All
>
> The Query below took around 175 Secs to return 22 Rows..Any way this query
> can be optimized
>

For analyzing your problem, you should send your table definition and an
output of the EXPLAIN statement.


Regards Georg

mysql, 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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Optimization

On 8 Jul 2002, at 16:07, Arul wrote:

> FROM
> User_Type_Details UTD,
> User_Type_Details B , Users U ,TempGeneralSearch1 T1,Users
> A LEFT JOIN Company C on (U.CompanyID = C.CompanyID) LEFT
> JOIN Expert_Info EI on (U.UserID = EI.UserID) LEFT JOIN
> User_Horizontal_Market UHM on (U.UserID = UHM.UserID) LEFT
> JOIN Industry I on (U.IndustryID = I.IndustryID) LEFT JOIN
> Technology_Area T on (U.Technology_AreaID =
> T.Technology_AreaID) LEFT JOIN Role R on (U.RoleID =
> R.RoleID )
> WHERE
> U.UserID = UTD.UserID
> AND U.User_Status='ACT' AND U.User_App_Status='APP' AND
> UTD.User_TypeID IN (4,0,2,3) AND T1.TempID=117

You seem to have some tables in the FROM part of your query that
aren't being used (Users aliased to A, User_Type_Details aliased to
B) and another that isn't joined to anything (T1, but that may be
okay since it looks like you may be getting only one record from it
because of the TempID=117). Eliminate the unused tables (which could
be resulting in a huge number of duplicated records), and your speed
should improve greatly.

--
Keith C. Ivey
Tobacco Documents Online
http://tobaccodocuments.org

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re:RE: Query Optimization

"Dave Dutcher" wrote on 24.07.2002 21:35:03:

>
>Hi, I did some more research on my problem. (Sorry for not doing it before
>I posted the other message), and I think perhaps my question could be
>summarized into the following.
>
>Can I use an index to speed up a select max() or select min()?
>
>I read what the manual has to say about a query like this:
>SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
>
>However I want to do this:
>SELECT MAX(key_part1) FROM table_name where key_part1 < 10
>
>also what I want to do is basically equivalent to this:
>
>SELECT key_part1 FROM table_name WHERE key_part1 < 10 ORDER BY key_part1
>DESC LIMIT 1
>
>In testing the second statement seems slower than the max() for some reason
>though.
>
>Is there anyway to get close to a O(logN) search time on these queries?
>
>Thanks again,
>
>Dave
>
>
>-----Original Message-----
>From: Dave Dutcher [mailto:djdutcher74@earthlink.net]
>Sent: Wednesday, July 24, 2002 1:04 PM
>To: mysql@lists.mysql.com
>Subject: Query Optimization
>
>
>I was wondering if somebody could give me any suggestions on how to optimize
>a query I am working on.
>
>This is my table:
>
>CREATE TABLE testdata (
> Begin char(9) NOT NULL default '',
> End char(9) NOT NULL default '',
> UNIQUE KEY BeginEndIndex (Begin,End)
>) TYPE=MyISAM;
>
>It is a table of ranges. i.e. 1-5, 7-11, 20000-24000. None of the ranges
>overlap, and I'm trying to write a query to find a range that contains a
>specific number such as 500,000. So this is what I've written for a query:
>
>select Begin, End
>from testdata
>where begin <= '005000000' and end >= '005000000'
>
>On a table with 100,000 records the explain command tells me it is using the
>BeginEndIndex, it says the key length is 9, and that it has to look through
>about 27,000 records. I would like to be able to configure the query or
>indexes , so it will only have to look through a couple records if possible.
>
>MySQL is super fast at a query like this:
>select Begin from testdata where Begin = '005000000';
>
>So I had the idea of trying this:
>select max(Begin) from testdata where Begin <= '005000000';
>
>I was hoping that it would do the same as the simple select, and then
>because it has a sorted index it wouldn't have to search the previous
>records to find the max, but explain still puts this at about 27,000
>records.
>
>Has anyone else tried writing a query similar to this? Does anybody have
>any suggestions?
>
>Thanks in advance,
>
>Dave
>
Hi Dave,

IMHO you can speed up your queries if you convert your columns to (unsigned)
int if possible. It should be much faster to compare two integers (4 bytes,2
assembler instructions) than comparing two strings (9 bytes,>10 assembler
instructions).

Bye Oliver
--
Good programming is 40% experience, 30% skill, 20% RTFM, 10% caffeine, and 5%
attention to detail.

Oliver Six, CEO
CAHOS GmbH, Cimbernstr. 51, Germany 81377 Muenchen
Phone +49 89 71 01 93 41, Fax +49 89 71 01 93 42


------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query Optimization

> From: Brandon Phelps
>=20
> I am curious if there is any way I can better optimize the below =
query, as currently it takes this query around 10 seconds to run but I =
am sure this will get slower and slower as the database grows.

You need an index on `close_dt`.

> SELECT
> open_dt,
> close_dt,
> protocol,
> INET_NTOA(src_address) AS src_address,
> src_port,
> INET_NTOA(dst_address) AS dst_address,
> dst_port,
> sent,
> rcvd
> FROM connections
> WHERE
> dst_port =3D 80
> ORDER BY close_dt DESC
> LIMIT 0, 30

----------------
Current farmers, who have become mere operators of machines and mixers =
of chemicals, may not have the skills to develop a local, sustainable =
agriculture. A new generation of farmers, numbering in the tens of =
millions, will need to be trained and relocated to rural communities. -- =
Pat Murphy
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg