Re: Backup and trimming data idea

[Rick James]

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.