Intro to indexing?

Hey, all. I'm trying to "get" indexing -- like, when do you specify an
index name during index creation, is index use implicit or explicit, and,
honestly, how exactly does it work, anyway? I've been RTFM'ing, but
haven't found anything that really laid it out in black and white;
usually, they'd give an example or two, but were awfully sparse on the
"whys and wherefores."

So, if anyone has something they could point me to -- electronic or dead
tree -- I'd be deeply appreciative.

Thanks!

-Ken


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Re: Intro to indexing?

--000e0cd305a8ff38a8046fba3894
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Mon, Jul 27, 2009 at 9:25 PM, Ken D'Ambrosio wrote:

> Hey, all. I'm trying to "get" indexing -- like, when do you specify an
> index name during index creation, is index use implicit or explicit, and,
> honestly, how exactly does it work, anyway? I've been RTFM'ing, but
> haven't found anything that really laid it out in black and white;
> usually, they'd give an example or two, but were awfully sparse on the
> "whys and wherefores."
>
> So, if anyone has something they could point me to -- electronic or dead
> tree -- I'd be deeply appreciative.
>
> Thanks!
>

Indexes can be on a single column or can span multiple columns (just like
keys). An index will be used when running a query, if the search is being
performed on the following:

- A single column that has a single-column index for example, if we index
departments on departmentID and perform a query like SELECT...WHERE
departmentID=n.
- A set of columns that forms a multicolumn index for example, if we have
created an index on the employee.assignment table on (clientID, employeeID,
workdate) and we perform a query like SELECT...WHERE clientID=x AND
employeeID=y AND workdate=z.
- A column or set of columns that forms a subset of a multicolumn index, as
long as there is a leftmost prefix of the index columns for example, with
the assignment table as before, with an index on (clientID, employeeID,
workdate), indexes would be used for these types of queries:


Source: MySQL Tutorial - SAMS Publishing

--
Muhammad Subair

--000e0cd305a8ff38a8046fba3894--

Re: Intro to indexing?

muhammad subair wrote:
> On Mon, Jul 27, 2009 at 9:25 PM, Ken D'Ambrosio wrote:
>
>> Hey, all. I'm trying to "get" indexing -- like, when do you specify an
>> index name during index creation, is index use implicit or explicit, and,
>> honestly, how exactly does it work, anyway? I've been RTFM'ing, but
>> haven't found anything that really laid it out in black and white;
>> usually, they'd give an example or two, but were awfully sparse on the
>> "whys and wherefores."
>>
>> So, if anyone has something they could point me to -- electronic or dead
>> tree -- I'd be deeply appreciative.
>>
>> Thanks!
>>
>
> Indexes can be on a single column or can span multiple columns (just like
> keys). An index will be used when running a query, if the search is being
> performed on the following:
>
> - A single column that has a single-column index for example, if we index
> departments on departmentID and perform a query like SELECT...WHERE
> departmentID=n.
> - A set of columns that forms a multicolumn index for example, if we have
> created an index on the employee.assignment table on (clientID, employeeID,
> workdate) and we perform a query like SELECT...WHERE clientID=x AND
> employeeID=y AND workdate=z.
> - A column or set of columns that forms a subset of a multicolumn index, as
> long as there is a leftmost prefix of the index columns for example, with
> the assignment table as before, with an index on (clientID, employeeID,
> workdate), indexes would be used for these types of queries:
>
>
> Source: MySQL Tutorial - SAMS Publishing
>


When you set up the table, you must have a primary index.
You make your best guess as to what would make it easier for the
sql engine to find the data you want, but the sql engine, in its
own wisdom will decide whether or not to use an index. You do
not explicitly tell it to do a select using an index.

Considerations:
every time you do an insert, replace, or update of data
included in an index, the index needs to be updated - which takes
a small amount of time.
indexes take space on disk - usually not a problem.
if the engine can use an index, finding is much faster.
if the database is small, who cares.


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