Re: Backup and trimming data idea
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.