it's very small project but i don't have time.
You will be using MySQL to obtain query execution plans and profile queries to explore the benefit of indexes. MySQL provides the command “explain’ to obtain the execution plan of queries. We will be looking at the query execution plan to learn if indexes are used to execute the query.
To view a query execution plan, you must prefix it with “Explain” command.
From this table, you can know if an index is used to execute the query.
Additionally, you will be using the command “show profiles” to obtain the query actual runtime (make sure to enable profiling in advance, see the Prerequisites Section.)
1. Install MySQL.
2. Create two tables: Students and Courses as follows:
create table Student (sid int, sname varchar(100));
create table Course (cid int, cname varchar(100));
3. Enable profiling by executing “set profiling = 1;”
In this lab, you will evaluate queries with 1,000 and 1,000,000 data entries. First, populate Students and Courses tables with 1,000 rows for each table. sid and cid are sequence numbers —i.e., 1 for the first row, 2 for the second row and so forth. sname and cname may be populated with random data.
For the second task of the assignment (see below), you will be inserting 1,000,000 rows for each table instead of 1,000.
Below, you are given a set of queries to evaluate and enhance their performance with 1,000 and 1,000,000 rows.
1. With 1,000 Rows
For each query, report its execution plan and runtime without any indexes to be your baseline. Next, add at least one index that should be beneficial for each query to enhance its performance. Your list of possible indexes must include - but not limited to - hash, B-tree and composite indexes.
For each step, include the query execution plan and profiled duration in your report by taking screenshots. They should be your guideline whether your added index is beneficial or not. Additionally, you must write your explanation and justification for each step. (explain why you think an index is useful/not useful)
2. With 1,000,000 Rows
Repeat task 1 after inserting 1,000,000 rows for each table instead of 1,000.
1. Select * From Students where sid=5;
2. Select * From Students where sname like ‘%s%’;
3. Select * From Students where sid >5 and sid < 100
4. Select * From Students;
5. Select * From Students where sid=5 and sname=’Ahmad’
6. Select * From Students, Courses where sid=cid
5 freelanceria on tarjonnut keskimäärin $85 tähän työhön
Hi I have 15 years of experience in MS SQL Server database programming. I will be able to help you with the project. Please ping me offline to discuss further.