When esky Well statement has a where clause in it.
The database will retrieve all the records that match the condition.
To do this it has to scan through the entire table looking at every single record to see if it matches
if the table has a lot of roles in it.
Then this could take an unacceptably long time.
There is a way to fix this however.
First let's imagine a book perhaps one with many pages.
If you want to look up something specific you wouldn't scan through every page of the entire book looking
for it.
Instead you use the index the index lists all the pages that match a certain topic or item using the
index.
You can find all the pages that contain something about that topic very quickly.
It's the same with the database.
We can create an index on one or more columns so that when we select some data using a web class the
condition in the WHERE clause uses the index to find the matching rows.
Rather than scanning the entire table.
This is much much faster.
In our articles table if we look at the structure of the table down here the tables indexes is listed.
We've already got an index on the idees column which was created when we assigned to this field to be
a primary key when we created the table.
So any where clause that uses the D column is already using this index.
So all those queries are okay.
However if we query the database using the title column where we don't have an index the database has
had to scan the entire table looking for matches.
We dont notice with so few records.
But if we had a database with hundreds of thousands of records then we'd certainly notice how slow it
was.
To fix this let's add an index on the title column in the structure tab in the indexes section.
Let's create an index on one column.
You can add an index name if you like.
I'll leave this blank.
And the database server will fill it in for you so we'll do that for the type of index we have various
options.
We already have a primary index on the idees column and you can only have one primary key so we dont
want that ignoring the last two options.
For now we can either create a unique index largest index unique index will only allow 1 rijkaard to
have a particular value for that field as more than one article could in theory have the same title.
We dont want that so we just want a regular index.
Then we need to select the column which is the title column and click go and that's it.
The database then maintains the indexes after magically in the background.
So once you've defined the index you dont have to do anything else.
So now when we query the table using the title field in the where class the query uses the index.
Not noticeable with so few records in a table.
But if we had many more we'd certainly notice the difference.
So when ever you use a web class in a query any columns in the WHERE clause need to have an index.
Otherwise your database queries could be very slow.
Another use of indexes is too hard data.
When you select data from a database the order of the records in the resultset is not guaranteed.
Theoretically they could come back in any order if you want to them in a specific Barder then you need
to include an order by in the ESC you L statement you couldn't order the resultset by one or more columns
by default.
Results are cited in ascending order to sort of results in descending order at the desk keyword.
After the column name.
If we select all the records from the article table the data comes back in this order.
Let's do this by title
and now the resultset is in Title arda alphabetically.
To do it in reverse alphabetical order.
We add desk after the column like that.
Dont forget you need an index on any column you are ordering by.
Otherwise the database has a lot more work to do to start the entire resultset before it sent it back
to you.
Note that inserting updating data in a table with indexes takes more time than with the table without
because the indexes also need to be updated so only create indexes and columns that will be frequently
searched are used for ordering.