MySQL Forums
Forum List  »  Backup

Re: Backup and trimming data idea
Posted by: Rick James
Date: June 27, 2012 08:36AM

Designing an index...

Example 1:

> INDEX(compID, dateTimeStartJourney) isnt we looking for linkID here?
> WHERE (dateTimeStartJourney between '".$b. "' And '".$e."' And tblLink.compID=$cID

To design an index:
1. Collect the field(s) that are tested via "=" in the WHERE clause. (In this case, `compID`.)
2. Collect one more field. (`dateTimeStartJourney`, which is used in a "range" (BETWEEN)).

That leads to
INDEX(compID, dateTimeStartJourney)

The query will use that to efficiently find the row(s), which then leads to other fields needed (`linkID`)


Example 2:

> WHERE tblEventAlert.associateID=".$aID." Order By tblEventAlert.eventAlertDateTime Asc
> INDEX(linkID, eventAlertDateTime). I dont understand why is the index needed

This is a variant wherein you do
1. Collect fields with '=' from WHERE
2. If step 1 included the entire WHERE clause, then move on to collect fields from the ORDER BY.

This will execute thus:
1. Drill into the index to find $aID -- very fast, regardless of dataset size.
2. Scan the rows (in the index) -- these will be in the required "order"; hence no need to later sort the results.
3. For each index row, look up (in the data) any other info needed.


Example 3:

If the SELECT is using fields a and b to find c, while touching no other columns, then
INDEX(a,b,c) or INDEX(b,a,c)
could be optimal. But, having c first in the INDEX makes the index useless for that query.

Even for a table with under a thousand rows, you should build the appropriate indexes.

Options: ReplyQuote


Subject
Views
Written By
Posted
3508
June 22, 2012 07:34PM
1687
June 23, 2012 05:23PM
1745
June 23, 2012 05:36PM
1763
June 24, 2012 01:51PM
1796
June 25, 2012 10:16AM
1650
June 26, 2012 08:14AM
1746
June 26, 2012 08:46AM
Re: Backup and trimming data idea
1630
June 27, 2012 08:36AM
1730
June 27, 2012 09:03AM
1593
June 28, 2012 11:19PM
1546
June 29, 2012 07:12AM
1549
June 30, 2012 11:50AM
1625
July 02, 2012 09:47AM
1567
July 03, 2012 01:32PM
1614
July 05, 2012 11:13AM
1784
July 06, 2012 09:57AM
1572
July 07, 2012 10:50AM
1595
July 08, 2012 09:33AM
1585
July 16, 2012 08:08PM
1564
July 21, 2012 10:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.