Test Your MySQL Cardinality Knowledge with Our SQL Quiz

Progress

True / False

In MySQL, cardinality refers to the uniqueness of data values in a column.

True False

Cardinality is a measure of the number of distinct values in a column, indicating the uniqueness of the data.

True / False

High cardinality in MySQL means a column has a large number of duplicate values.

True False

High cardinality means a column has many unique values, while low cardinality means it has many duplicate values.

True / False

The SHOW INDEX command in MySQL can display cardinality information for an index.

True False

The SHOW INDEX command includes cardinality as part of the output, showing the number of unique values in the index.

True / False

In MySQL, a column with low cardinality is ideal for indexing.

True False

Columns with high cardinality are generally better suited for indexing because they have more unique values, making the index more efficient.

True / False

Cardinality in MySQL can be affected by the presence of NULL values in a column.

True False

: NULL values are considered distinct values, so their presence can affect the cardinality of a column.

True / False

The cardinality of an index in MySQL can change over time as data is inserted, updated, or deleted.

True False

As the data in a table changes, the number of unique values in the indexed columns can change, affecting the cardinality.

True / False

MySQL automatically updates the cardinality of an index in real-time as data is modified.

True False

MySQL does not always update the cardinality in real-time; it is often updated during operations like ANALYZE TABLE.

True / False

Cardinality estimates in MySQL can be inaccurate, leading to suboptimal query plans.

True False

Inaccurate cardinality estimates can cause the MySQL optimizer to choose less efficient query execution plans.

True / False

The InnoDB storage engine in MySQL uses cardinality estimates to determine whether to use a table scan or an index scan.

True False

The MySQL optimizer, including the InnoDB storage engine, uses cardinality estimates to decide the most efficient way to execute a query.

Q10

True / False

To improve query performance in MySQL, it is always necessary to maintain high cardinality for all columns.

True False

Maintaining high cardinality is important for indexed columns but not necessarily for all columns. Some columns, like status flags, naturally have low cardinality and do not need high uniqueness to function efficiently.

Q11

Single Choice

What is cardinality in the context of MySQL?

The number of tables in a database The number of unique values in a column The number of rows in a tableThe number of databases on a server

Cardinality refers to the number of unique values in a column, which helps in optimizing query performance.

Q12

Single Choice

Which MySQL command would you use to analyze the cardinality of an index?

SHOW TABLES SHOW INDEX FROM table_name DESCRIBE table_nameSHOW DATABASES

The SHOW INDEX FROM table_name command provides information about the indexes, including the cardinality.

Q13

Single Choice

High cardinality in a column means:

Few unique values Many unique values No unique valuesColumn is not indexed

High cardinality indicates that the column contains many unique values.

Q14

Single Choice

How does MySQL use cardinality information when optimizing queries?

It determines the number of tables to join. It chooses the index to use. It decides the storage engine.It sets the buffer size.

MySQL uses cardinality information to decide which index to use, as higher cardinality indexes are often more selective and efficient for queries.

Q15

Single Choice

In MySQL, which of the following best describes the relationship between cardinality and performance?

Higher cardinality always improves performance. Lower cardinality always improves performance. The optimal cardinality depends on the query and dataset.Cardinality does not affect performance.

The optimal cardinality depends on the query and dataset because different scenarios may benefit from different cardinality levels.

Q16

Single Choice

What would be the cardinality of a column in MySQL with the following values: [1, 2, 2, 3, 3, 3]?

1 2 34

There are 3 unique values (1, 2, 3), so the cardinality is 3.

Q17

Single Choice

When creating an index in MySQL, which type of index would benefit most from high cardinality?

FULLTEXT index UNIQUE index PRIMARY KEY indexSPATIAL index

A PRIMARY KEY index benefits from high cardinality because it ensures each row is uniquely identifiable, which improves search efficiency.

Q18

Single Choice

How does MySQL's InnoDB storage engine estimate index cardinality?

By scanning the entire table By sampling a subset of table rows By using user-defined statisticsBy checking foreign key constraints

InnoDB estimates index cardinality by sampling a subset of table rows, which provides a good approximation without the need to scan the entire table.

Q19

Single Choice

Which MySQL statement would you use to force the recalculation of index cardinality statistics?

ANALYZE TABLE table_name OPTIMIZE TABLE table_name REPAIR TABLE table_nameTRUNCATE TABLE table_name

The ANALYZE TABLE table_name statement updates index statistics, including cardinality, to help the query optimizer make better decisions.

Q20

Single Choice

In a MySQL certification exam scenario, how would you describe the impact of low cardinality on a composite index?

It reduces the index size significantly. It makes the index less selective and may degrade performance. It ensures better query performance.It is irrelevant to query performance.

Low cardinality makes the index less selective, which can lead to less efficient query execution as more rows may need to be scanned.

Q21

Multiple Choice

Which SQL code correctly calculates the cardinality of a 'department_id' column in the 'employees' table?

SQL Code

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; SELECT department_id, COUNT(DISTINCT department_id) FROM employees; SELECT COUNT(department_id) FROM employees;SELECT COUNT(*) FROM employees GROUP BY department_id;

Calculating cardinality involves counting the number of occurrences of each unique value in a column. The GROUP BY clause groups the records by 'department_id', and COUNT(*) returns the number of records in each group.

Q22

Multiple Choice

Which SQL statement effectively analyzes the cardinality of the 'customer_id' column in a sales table?

SQL Code

SELECT customer_id, COUNT(*)
FROM sales
GROUP BY customer_id;

SELECT customer_id, COUNT(*) FROM sales GROUP BY customer_id; SELECT customer_id, SUM(amount) FROM sales GROUP BY customer_id; SELECT COUNT(DISTINCT customer_id) FROM sales;SELECT customer_id FROM sales;

To analyze cardinality, the SQL query groups the data by 'customer_id' and counts the number of sales for each customer, providing insights into the distribution of sales across different customers.

Q23

Multiple Choice

Which SQL query correctly evaluates the cardinality of an index on the 'product_id' column in a 'sales' table?

SQL Code

SHOW INDEX FROM sales
WHERE Key_name = 'product_id';

SHOW INDEX FROM sales WHERE Key_name = 'product_id'; SELECT COUNT(DISTINCT product_id) FROM sales; SHOW INDEX FROM sales WHERE product_id = 'index';SELECT COUNT(product_id) FROM sales;

In MySQL, the SHOW INDEX statement provides information about the indexes on a table, including their cardinality, which indicates the number of unique values in the indexed column.

Q24

Multiple Choice

Which SQL query accurately assesses the impact of cardinality on a database query's performance?

SQL Code

EXPLAIN SELECT *
FROM employees
WHERE department_id = 10;

EXPLAIN SELECT * FROM employees WHERE department_id = 10; SELECT COUNT(DISTINCT department_id) FROM employees; EXPLAIN SELECT COUNT(*) FROM employees WHERE department_id = 10;SELECT * FROM employees WHERE department_id = 10;

The EXPLAIN statement in MySQL provides insights into how a query will be executed, including the cardinality of indexed columns, which can impact the efficiency and performance of the query.

Q25

Multiple Choice

Which SQL code snippet best illustrates high cardinality in a table's 'email' column?

SQL Code

SELECT email, COUNT(*)
FROM users
GROUP BY email;

SELECT email, COUNT(*) FROM users GROUP BY email; SELECT COUNT(DISTINCT email) FROM users; SELECT email FROM users;SELECT email FROM users WHERE email LIKE '%@gmail.com';

High cardinality refers to columns that contain a large number of unique values. The SQL query groups the data by 'email' and counts the number of occurrences of each email address.

Q26

Multiple Choice

What SQL code correctly identifies low cardinality in the 'gender' column of a 'users' table?

SQL Code

SELECT gender, COUNT(*)
FROM users
GROUP BY gender;

SELECT gender, COUNT(*) FROM users GROUP BY gender; SELECT COUNT(DISTINCT gender) FROM users; SELECT gender FROM users;SELECT COUNT(*) FROM users;

Low cardinality refers to columns with few unique values. In the 'gender' column, there are typically only a few distinct values (e.g., 'Male', 'Female'), so grouping by 'gender' and counting the records provides an analysis of its cardinality.

Q27

Multiple Choice

Which SQL code correctly calculates the cardinality of a 'department_id' column in the 'employees' table?

SQL Code

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

Q28

Multiple Choice

Which SQL query correctly evaluates the cardinality of an index on the 'product_id' column in a 'sales' table?

SQL Code

SHOW INDEX FROM sales
WHERE Key_name = 'product_id';

SHOW INDEX FROM sales WHERE Key_name = 'product_id'; SELECT COUNT(DISTINCT product_id) FROM sales; SHOW INDEX FROM sales WHERE product_id = 'index';SELECT COUNT(product_id) FROM sales;

In MySQL, the SHOW INDEX statement provides information about the indexes on a table, including their cardinality, which indicates the number of unique values in the indexed column.

Q29

Multiple Choice

Choose the SQL code that demonstrates the calculation of cardinality for a composite index on 'first_name' and 'last_name' columns.

SQL Code

SHOW INDEX FROM employees
WHERE Key_name = 'first_name_last_name';

SHOW INDEX FROM employees WHERE Key_name = 'first_name_last_name'; SELECT COUNT(DISTINCT first_name, last_name) FROM employees; SHOW INDEX FROM employees WHERE Key_name = 'first_name';SELECT COUNT(*) FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

In MySQL, the SHOW INDEX statement can be used to calculate the cardinality of a composite index, which involves two or more columns, such as 'first_name' and 'last_name'.

Q30

Multiple Choice

What SQL code correctly identifies low cardinality in the 'gender' column of a 'users' table?

SQL Code

SELECT gender, COUNT(*)
FROM users
GROUP BY gender;

SELECT gender, COUNT(*) FROM users GROUP BY gender; SELECT COUNT(DISTINCT gender) FROM users; SELECT gender FROM users;SELECT COUNT(*) FROM users;

MySQL Database Quiz Questions