473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

newbe Q: how to optimize this query?

>From the following MySQL command:

EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name
= t1.name WHERE t1.id IN(123, 124);

which result is:
-------------------------------------------------------------------------------------
table type possible_key key key_len ref rows Extra
t1 const PK, name PK 4 const 10
t3 const PK PK 4 const 10
t2 ref PK PK 4 const 500
Using Where
-------------------------------------------------------------------------------------
where PK is PRIMARY, how can I optimize this query??

It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???
From the resulting EXPLAIN list, I thought I used only indexed PK on

the query, the PK is possibly t1.id or the composite of (id,name)???
how can I know if I had used indexes of both columns(id, name)??

Is there any ways that I should take to optimize this query??

Thank you very much for your input and hints.

Huaer

Mar 8 '06 #1
4 2007
<Hu******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???


The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.

Regards,
Bill K.
Mar 8 '06 #2
Bill Karwin wrote:
<Hu******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???


The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.


Hi, Bill:
Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..

Many thanks..
Huaer

Mar 8 '06 #3
"huaer" <Hu******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..


If I do, do I get the job?

Bill K.
Mar 8 '06 #4
huaer wrote:
Bill Karwin wrote:
<Hu******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.google groups.com...
It looks to me that for table t1, there are two possible keys, t1.id
and t1.name, since both of them are used in the query, so both of them
should be indexed to optimize the query.. But MySQL uses only one index
per-query and per-table, so I have to use a composite index on table
"t1"?? Is this correct???


The MySQL optimizer tries to make the best choices. Even if you had another
index (and suppose MySQL could employ more than one index per table), it
might choose not to use it, if it estimated that using it would cost more
than it would benefit.

For instance, if the PK index is used to reduce the rows to a small number,
then it's very inexpensive to do the remaining JOIN with a full table-scan.
In such cases, it might actually be cost more than it saves to load another
index into memory. The MySQL optimizer tries to notice things like that and
may choose not to use an index even if one is available.

Here's an analogy: if you drive your car 20 miles to a gas station that has
cheaper prices, but you burn more gas than you save by doing so. Or you do
this when your tank is nearly full anyway, so the amount of cost benefit is
very little.

Hi, Bill:
Thank you very much for your info. :-) but this is actually an
interview question, so I should have to find a way to optimize it
instead of letting MySQL do it..:-(.. would you please give me some
more hints especially about using EXPLAIN or composite indexes?? I had
read the documentation about optimizating SELECT on MySQL AB's website,
but still cannot make sure about the solutions..

Many thanks..
Huaer

then are you really the right candidate for the job? :)
Mar 8 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
6
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
5
by: xeqister | last post by:
Greetings all, We have a complicated statement in DB2 which takes long hour to complete and we have created most of the indexes. Does anybody knows how to tune the following statement to optimize...
3
by: Reddy | last post by:
The sql query for my datagrid returns 100, 000 records. But the datagrid should display 20 records per page. I am using datagrid paging, but it is taking too much time for the page to load. Is...
13
by: Frank Swarbrick | last post by:
IBM has a product for the VSE operating system called the VSAM Redirector. It allows you to use VSAM to access RDBMS tables/views as if they were actual VSAM files. We're doing a comparison right...
17
by: Eric_Dexter | last post by:
def simplecsdtoorc(filename): file = open(filename,"r") alllines = file.read_until("</CsInstruments>") pattern1 = re.compile("</") orcfilename = filename + "orc" for line in alllines: if not...
5
by: troy | last post by:
Could someone please point me in the right direction on how to read in a large query with .net. I am trying to emulate a legacy database system so I don't know the upper bounds of the sql query....
1
by: acornejo | last post by:
Hi All I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each...
3
zabsmarty
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.