How an index can improve the performance

Assignment Help Database Management System

Reference no: EM133055438

This database contains a single table that shows the California population projection by county, age, gender and ethnicity. We will use this database to demonstrate how an index can improve the performance of database queries.

How many rows of data does this table contain?
Create a query that returns the information for the expected females in Kings county in the year 2024 that have a white, non-Hispanic ethnicity. Execute this query several times and determine the average time that it takes to run this query. The MySql workbench window will provide you with the execution time. Execute the query a minimum of 3 times and get an average value for the execution time
Use the mysql workbench Execution Plan button to display the following:
- What type of scan does the query perform?
- How many rows were scanned to return this data?
Indexes can improve the performance of your database by having an index on fields used in search criteria.
After creating the index, issue the following analyse statement to update the database with information on the newly created index. analyze table ca_populationprojection_large;
Rerun your search query from step 2)
Execute the query a minimum of 3 times and get an average value for the execution time
- Does your query run better?
- What type of scan does your query use now?
- How many rows were scanned after the index was created?
Create a view that returns the rows for all the data for the year 2024. You can call this view vw_Year2024. Use this view in a query to return the same data set from question 2.
Document your solution to this activity in a word document and post your answers into Module 5.1 mi602_ca_stats database discussion forum.
Review another student's answers and provide feedback. Is their index the same as yours? Which index do you think works better i.e. does your index produce a better performance improvement?
Your learning facilitator will provide feedback on the answers during the class

Attachment:- Ca_stats_database.zip

Reference no: EM133055438

Questions Cloud

Prepare a schedule of cash receipts from sales for April : X-Tel budgets sales of $102,000 for April, $174,000 for May, and $114,000 for June. Prepare a schedule of cash receipts from sales for April

Prepare the journal entry to record the purchase : Land is appraised at $234,000; land improvements are appraised at $78,000; Prepare the journal entry to record the purchase

Maintain good community relations : What environmental laws will apply to the new plant construction? What steps should you take to maintain good community relations?

What is the present worth of the contract : What is the present worth of the contract (in year 0) if the company will receive a total of nine $13,000 payments beginning in year 2 and ending in year 10

How an index can improve the performance : Demonstrate how an index can improve the performance of database queries - Create a query that returns the information for the expected females in Kings county

Sole proprietors personally liable for debts : Why did the court apply the filing requirement so literally? Why are sole proprietors personally liable for the debts of their business?

Determine the predetermined overhead rate for July : The company's budgeted fixed manufacturing overhead is $95,460 per month, Determine the predetermined overhead rate for July

Identify the governmental services : Can you identify the governmental services that have been provided to you since you got up this morning?

By how much the Investment in Singleton Ltd account change : Under equity accounting, by how much would the 'Investment in Singleton Ltd' account change for the year ended 31 December 20X0

User Account

All Pages