How many indexes does a two

Indexing is the way to get an unordered table into an order that will maximize the query’s efficiency while searching.

When a table is unindexed, the order of the rows will likely not be discernible by the query as optimized in any way, and your query will therefore have to search through the rows linearly. In other words, the queries will have to search through every row to find the rows matching the conditions. As you can imagine, this can take a long time. Looking through every single row is not very efficient.

For example, the table below represents a table in a fictional datasource, that is completely unordered.

If we were to run the following query:

SELECT
	company_id,
	units,
	unit_cost
FROM
	index_test
WHERE
	company_id = 18

The database would have to search through all 17 rows in the order they appear in the table, from top to bottom, one at a time. So to search for all of the potential instances of the company_id number 18, the database must look through the entire table for all appearances of 18 in the company_id column.

This will only get more and more time consuming as the size of the table increases. As the sophistication of the data increases, what could eventually happen is that a table with one billion rows is joined with another table with one billion rows; the query now has to search through twice the amount of rows costing twice the amount of time.

You can see how this becomes problematic in our ever data saturated world. Tables increase in size and searching increases in execution time.

Querying an unindexed table, if presented visually, would look like this:

What indexing does is sets up the column you’re search conditions are on in a sorted order to assist in optimizing query performance.

With an index on the company_id column, the table would, essentially, “look” like this:

Now, the database can search for company_id number 18 and return all the requested columns for that row then move on to the next row. If the next row’s comapny_id number is also 18 then it will return the all the columns requested in the query. If the next row’s company_id is 20, the query knows to stop searching and the query will finish.

How does Indexing Work?

In reality the database table does not reorder itself every time the query conditions change in order to optimize the query performance: that would be unrealistic. In actuality, what happens is the index causes the database to create a data structure. The data structure type is very likely a B-Tree. While the advantages of the B-Tree are numerous, the main advantage for our purposes is that it is sortable. When the data structure is sorted in order it makes our search more efficient for the obvious reasons we pointed out above.

When the index creates a data structure on a specific column it is important to note that no other column is stored in the data structure. Our data structure for the table above will only contain the the company_id numbers. Units and unit_cost will not be held in the data structure.

How Does the Database Know What Other Fields in the Table to Return?

Database indexes will also store pointers which are simply reference information for the location of the additional information in memory. Basically the index holds the company_id and that particular row’s home address on the memory disk. The index will actually look like this:

With that index, the query can search for only the rows in the company_id column that have 18 and then using the pointer can go into the table to find the specific row where that pointer lives. The query can then go into the table to retrieve the fields for the columns requested for the rows that meet the conditions.

This declares an array with the specified size, named variableName, of type typeName.  The array is indexed from 0 to size-1. The size (in brackets) must be an integer literal or a constant variable. The compiler uses the size to determine how much space to allocate (i.e. how many bytes).

Examples:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

The last example illustrates a two dimensional array (which we often like to think about as a table). We usually think of the first size as rows, and the second as columns, but it really does not matter, as long as you are consistent! So, we could think of the last declaration as a table with 5 rows and 10 columns, for example.

Initializing Arrays:

  int x; 
  x = 0; 

Or, we could simply initialize the variable in the declaration statement itself:

  int x = 0; 

Can we do the same for arrays? Yes, for the built-in types. Simply list the array values (literals) in set notation { } after the declaration. Here are some examples:

  int list[4] = {2, 4, 6, 8}; 
  char letters[5] = {'a', 'e', 'i', 'o', 'u'}; 
  double numbers[3] = {3.45, 2.39, 9.1}; 
  int table[3][2] = {{2, 5} , {3,1} , {4,9}}; 

C-style strings

• We use strings frequently, but there is no built-in string type in the language
• A C-style string is implemented as an array of type char that ends with a special character, called the "null character".
  • The null character has ASCII value 0
  • The null character can be written as a literal in code this way: '\0'
• Every string literal (something in double-quotes) implicitly contains the null character at the end

Since character arrays are used to store C-style strings, you can initialize a character array with a string literal (i.e. a string in double quotes), as long as you leave room for the null character in the allocated space.

  char name[7] = "Johnny";

Notice that this would be equivalent to:

  char name[7] = {'J', 'o', 'h', 'n', 'n', 'y', '\0'};

Variations in initializing

Array declarations must contain the information about the size of the array. It is possible to leave the size out of the [ ] in the declaration as long as you initialize the array inline, in which case the array is made just large enough to capture the initialized data. Examples:

  char name[] = "Johnny";        // size is 7
  int list[] = {1, 3, 5, 7, 9};  // size is 5

Another shortcut with initializer sets is to use fewer elements than the size specifies. Remaining elements will default to 0. It is illegal to use a set containing more elements than the allocated size.

  int list[5] = {1, 2};         // array is {1, 2, 0, 0, 0}
  int nums[3] = {1, 2, 3, 4};   // illegal declaration.

Note: Using initializers on the declaration, as in the examples above, is probably not going to be as desirable with very large arrays.
Another common way to initialize an array -- with a for loop:
This example initializes the array numList to {0, 2, 4, 6, 8, 10, 12, 14, 16, 18}.

  int numList[10];
  int i;
  for (i = 0; i < 10; i++)
     numList[i] = i * 2;

Using Arrays:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

It would not be appropriate, however, to use an array index that is outside the bounds of the valid array indices:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

The statement above is syntactically legal, however. It is the programmer's job to make sure that out of bounds indices are not used. Do not count on the compiler to check it for you -- it will not!

Copying arrays:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

With variables, we use the assignment statement, so this would be the natural tendency -- but it is wrong!

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

We must copy between arrays element by element. A for loop makes this easy, however:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

Simple I/O with strings:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

Strings can also be output and input in their entirety, with the standard input and output objects (cin and cout):
The following line outputs the word "Hello":

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

The following line allows the entry of a word (up to 19 characters and a terminating null character) from the keyboard, which is stored in the array word1:

  int list[30];            // an array of 30 integers 
  char name[20];           // an array of 20 characters 
  double nums[50];         // an array of 50 decimals 
  int table[5][10];        // a two dimensional array of integers 

Characters are read from the keyboard until the first "white space" (space, tab, newline, etc) character is encountered.  The input is stored in the character array and the null character is automatically appended.

How many indexes should you have?

What are the 3 types of indexes?

What are the 4 basic indexes?

How many indexes do we have?