473,854 Members | 1,637 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

optimize query??

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
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...an d the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce
Jul 20 '05 #1
6 2134
I should have mentioned...the re are 1 million rows in the table kbm.
The "State = 'MA'" query returns 997,999 rows. The "Zipcode = '01001'"
query returns 15,673.
I have no idea if this even matters.

-bruce
"Bruce D" <br************ *@hotmail.com> wrote in message
news:10******** *****@corp.supe rnews.com...
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
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...an d the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce

Jul 20 '05 #2
Bruce D wrote:
I should have mentioned...the re are 1 million rows in the table kbm.
The "State = 'MA'" query returns 997,999 rows. The "Zipcode = '01001'"
query returns 15,673.
I have no idea if this even matters.


It does matter. Since the query returns 99.8% of the rows in the table
anyway, MySQL has decided that using the index would only be extra work
with very little gain.

http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html says:

"Sometimes MySQL will not use an index, even if one is available. One
way this occurs is when the optimizer estimates that using the index
would require MySQL to access a large percentage of the rows in the
table. (In this case, a table scan is probably much faster, because it
will require many fewer seeks.)"

Regards,
Bill K.
Jul 20 '05 #3
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:co******** @enews1.newsguy .com...

It does matter. Since the query returns 99.8% of the rows in the table
anyway, MySQL has decided that using the index would only be extra work
with very little gain.

http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html says:

"Sometimes MySQL will not use an index, even if one is available. One
way this occurs is when the optimizer estimates that using the index
would require MySQL to access a large percentage of the rows in the
table. (In this case, a table scan is probably much faster, because it
will require many fewer seeks.)"

Regards,
Bill K.


That makes sense. Thanks Bill!
-bruce
Jul 20 '05 #4

"Bruce D" <br************ *@hotmail.com> wrote in message
news:10******** *****@corp.supe rnews.com...
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
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...an d the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce

There is no way you can optimize this query. I created a datbase where 90%
of entries were 'MA'. The query demands a table scan . So one solution is
'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff.
Not normalized nor a proper subtype, but runs real fast.

Rich
Jul 20 '05 #5
"Rich R" <rr***@cshore.c om> wrote in message
news:qQ******** *********@newss vr31.news.prodi gy.com...

There is no way you can optimize this query. I created a datbase where 90%
of entries were 'MA'. The query demands a table scan . So one solution is
'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff.
Not normalized nor a proper subtype, but runs real fast.

Rich

Thanks for the responses!
I'm running into another very similar problem...
This query runs great (returns 218,973 records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630701'
doing an explain shows that it is using the indexes.
But this query runs slow and does not use the indexes (returns 220,185
records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630630'

You've both stated that there MySQL decides not to use an index...but can I
force it to? The second query is returning in 11 seconds in CF...not
exceptable.

Any ideas on how I can optimize (if at all)??

TIA
-bruce
Jul 20 '05 #6

"Bruce D" <br************ *@hotmail.com> wrote in message
news:10******** *****@corp.supe rnews.com...
"Rich R" <rr***@cshore.c om> wrote in message
news:qQ******** *********@newss vr31.news.prodi gy.com...
There is no way you can optimize this query. I created a datbase where 90% of entries were 'MA'. The query demands a table scan . So one solution is 'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff. Not normalized nor a proper subtype, but runs real fast.

Rich

Thanks for the responses!
I'm running into another very similar problem...
This query runs great (returns 218,973 records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630701'
doing an explain shows that it is using the indexes.
But this query runs slow and does not use the indexes (returns 220,185
records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630630'

You've both stated that there MySQL decides not to use an index...but can

I force it to? The second query is returning in 11 seconds in CF...not
exceptable.

Any ideas on how I can optimize (if at all)??

TIA
-bruce


You just can't. I specified 'force indexes' but no go. And it's a smart idea
But when you think about what I did, it made no sense. The indexes were way
too dense. So a table scan is more efficient. I still recommend breaking out
'MA" into it's own table. 'MA" is 90% of your index. I'd do a table scan,
too!

Rich
Jul 20 '05 #7

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

Similar topics

0
1801
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 "optimize table", but (seemingly) the server version 4.0.16 requires INSERT as well. Is the INSERT privelege necessary for performing optimize in mysql
3
2272
by: Paul Janssen | last post by:
Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause resulting into overall time of <60 seconds; The table consists of 950.000 records, and the resultset consists of 205.000 records.
5
1789
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 the query? "SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM), AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3, YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6 WHERE...
3
2829
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 there any way I can optimize the speed. Any sample code would be great. Thanks, Reddy
4
2038
by: Huaer.XC | last post by:
>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
13
3131
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 now between Oracle, which we've been running in production for many years, and DB2, which we are just starting with. One of the tests I am trying is to see how efficient the VSAM Redirector works with DB2 versus Oracle. Below are two types of...
11
1955
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from table1 left join table2 using(id) left join table3 using (id)
1
1780
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 iteration. How can I optimize the mysql query in order to make it faster? table tblOutgoing has an unique id slog_id. if not, how can I add indexes to this table? this table is constantly queried (selects/updates/inserts) based on several field...
3
2506
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
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10672
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10744
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10364
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7077
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4150
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.