To gain performance, do I need to index ALL the fields in the where
clause.
Say we have a query like:
select stuff from table where field1=.. and field2=...
If field1 selection substantially reduces the number of records
selected, do we really need to index field2 as well.
In other words, does the query first subselect from the first argumet,
and then select further from the subset, or does the query do two
independent subselects and then merge them together?
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-indexi...ict156808.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=524834 2 2143
steve wrote: To gain performance, do I need to index ALL the fields in the where clause.
Say we have a query like: select stuff from table where field1=.. and field2=...
If field1 selection substantially reduces the number of records selected, do we really need to index field2 as well.
In other words, does the query first subselect from the first argumet, and then select further from the subset, or does the query do two independent subselects and then merge them together?
MySQL is clever enough to work out which columns have indexes on them and
will use those columns first when selecting the data, and the columns that
do not have indexes afterwards, even if you put the where clause in an
order where the first column listed does not have an index.
You are correct in assuming that if the indexed column does most of the work
then you don't really need to index the second column as well, otherwise
you might well end up with every column in the table indexed! You would
probably only need to have the second column indexed as well if a select
query for the first column returned several hundred or thousands of
records.
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
"Chris Hope" wrote: steve wrote:
To gain performance, do I need to index ALL the fields in the
where clause.
Say we have a query like: select stuff from table where field1=.. and field2=...
If field1 selection substantially reduces the number of records selected, do we really need to index field2 as well.
In other words, does the query first subselect from the first argumet, and then select further from the subset, or does the query do two independent subselects and then merge them together?
MySQL is clever enough to work out which columns have indexes on
them and will use those columns first when selecting the data, and the
columns that do not have indexes afterwards, even if you put the where clause in
an order where the first column listed does not have an index.
You are correct in assuming that if the indexed column does most of the work then you don’t really need to index the second column as well, otherwise you might well end up with every column in the table indexed! You would probably only need to have the second column indexed as well if a select query for the first column returned several hundred or thousands of records.
Chris, thanks for the quick reply. That is exactly the info I was
looking for. Good to hear that the order of fields in the where
clause does not matter, and mysql can figure out how to optimize.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-indexi...ict156808.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=524872 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Danny |
last post by:
sorry for posting a question that has probably been posted hundreds of
times, but i can't seem to find the answer i need...We're using a Sql
Server 7.0 database and recently started getting timeout expired
errors on our views, which i'm assuming is due to the volume of
records in our tables. Unfortunately i'm not too experiences w/
indexing tables, so my question is:
1) how do you determine which fields to index (does it have to be a...
|
by: Thomas Bartkus |
last post by:
If we have a date/time field and are doing frequent queries
WHERE {date/time field} BETWEEN TimeA AND TimeB
Does it make sense, query speed wise, to create an index on the date/time
field?
The reason I ask is that TimeA and TimeB are significant down to seconds.
My *assumption* is that with a large amount of data scatter very few records
will contain duplicate date/time points. And that under these circumstances,
indexing will be of...
|
by: Kent.Brooke |
last post by:
Can someone set me straight I know indexing is a "try & see" art.
However I am at a loss if it's better to use the INCLUDE switch on a
unique index and tag on the columns be used to avoid a lookup on the
disk. Or if it's better to use all of the columns as part of the index?
One more question, is it a good idea to always have the PK of the table
as part of any index created on a table? The querys don't actualy use
the PK column in their...
|
by: John Sidney-Woollett |
last post by:
Hi
I'm building a web app where changes to customer orders are logged in the
following table, and I worried about the time that it will take to locate
records that need further processing/actioning. Here's the table:
create table CUSTOMER.WCCustOrderStatusLog (
WCCustOrderID integer,
WCOrderStatusID integer,
-- date/time at which some process acknowledged the status
|
by: Hennie7863 |
last post by:
Hi everybody,
After days reading stuff about indexing, extents, pages, 8KB, 64 KB,
IGNORE_DUP_KEY, CREATE INDEX, bla bla, lalalala, lllllll, grrrrrrr and
testing with different kinds of queries, indexes, situations i'm
getting confused, irritated, etc.
SITUATION
I have the following situation. We have a fact table with 1,8 million
records (Testsituation) and i'm inserting 100000 records and i want to
| |
by: Bethany Holliday |
last post by:
Hi All,
I'm hoping someone can help me. I think I'm missing something very
basic. I'm trying to put a clustered index on a view that I have
created. I keep getting the error:
Server: Msg 8668, Level 16, State 1, Line 1
An index cannot be created on the view 'cew_avwage_uscnty' because the
select list of the view contains a non-aggregate expression.
|
by: Chung Leong |
last post by:
Here's the rest of the tutorial I started earlier:
Aside from text within a document, Indexing Service let you search on
meta information stored in the files. For example, MusicArtist and
MusicAlbum let you find MP3 and other music files based on the singer
and album name; DocAuthor let you find Office documents created by a
certain user; DocAppName let you find files of a particular program,
and so on.
Indexing Service uses plug-ins...
|
by: Amar |
last post by:
Hi All,
I need to select data from a database table containing huge amount of
data. Now I am storing data using one primary key and I am just using
simple select statement, and this process gives me the output but it is
taking long to execute the query. As much I had heared I want to use
some indexing or cluster indexing which might help me but I am not so
familiar with these things. So if any one having some solutions to
execute the...
|
by: Icemokka |
last post by:
Hi,
I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the commandbuilder adds a where clause that contains all
the fields in the datatable ( to check whether the record has been
changed since the fill ).
Because MSAccess does not allow more then 99 fields in the where
clause, the update fails offcourse.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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...
| |
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...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |