MySQL: SELECT with Index Hint option to Optimize the Query

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

MySQL: SELECT with Index Hint option to Optimize the Query

The Table Indexes are always the main focus to improve the performance of any SQL Query.
In MySQL, We can provide INDEX Hint to Query Optimizer for choosing our analyzed Index.

In most of the cases, Query Planner and Query Optimizer are accurate to choose perfect Index.
But sometimes, still It is required to give Index Hint for reducing the overall Query Planning Time.

If we know that this Index is good for this query, we should Provide the Hint to Query Optimizer.

MySQL supports command like USE INDEX, IGNORE INDEX, FORCE INDEX, which we can use for Index Hint.

The USE INDEX hint tells MySQL to use only one of the named indexes to find rows in the table.
The IGNORE INDEX tells MySQL to not use some particular index or indexes.
The FORCE INDEX hint acts like USE INDEX , with the addition that a table scan is assumed to be very expensive.

Prepared small demonstration on this.

Create a sample table:

1 2 3 4 5 6 7

CREATE TABLE tbl_EmployeeDetails ( EmpID INTEGER PRIMARY KEY ,EmpName VARCHAR(50) ,EmpSalary BIGINT ,DepartmentName VARCHAR(50) );

Insert few sample Records:

1 2 3 4 5 6 7 8 9 10

INSERT INTO tbl_EmployeeDetails VALUES (1,'Anvesh',80000,'Sales') ,(2,'Neevan',90000,'Sales') ,(3,'Jenny',50000,'Production') ,(4,'Roy',60000,'Production') ,(5,'Martin',30000,'Research') ,(6,'Mahi',85000,'Research') ,(7,'Kruti',45000,'Research') ,(8,'Manish',75000,'Research');

Create few sample Indexes:

1 2 3

CREATE INDEX idx_tbl_EmployeeDetails_EmpName ON tbl_EmployeeDetails (EmpName); CREATE INDEX idx_tbl_EmployeeDetails_DepartmentName ON tbl_EmployeeDetails (DepartmentName); CREATE INDEX idx_tbl_EmployeeDetails_EmpName_EmpSalary ON tbl_EmployeeDetails (EmpName,EmpSalary);

Using USE INDEX:

1 2 3 4 5 6 7

SELECT *FROM tbl_EmployeeDetails USE INDEX (idx_tbl_EmployeeDetails_EmpName) WHERE EmpName = 'Roy';   SELECT *FROM tbl_EmployeeDetails USE INDEX (idx_tbl_EmployeeDetails_EmpName_EmpSalary) WHERE EmpName = 'Roy' AND EmpSalary > 50000;

Using IGNORE INDEX:

1 2 3

SELECT *FROM tbl_EmployeeDetails IGNORE INDEX (idx_tbl_EmployeeDetails_EmpName) WHERE EmpName = 'Roy' AND EmpSalary > 50000;

Using FORCE INDEX:

1 2 3

SELECT *FROM tbl_EmployeeDetails FORCE INDEX (idx_tbl_EmployeeDetails_EmpName) WHERE EmpName = 'Roy' AND EmpSalary > 50000;

SQL Server : Why is the XLOCK table hint broken?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Why is the XLOCK table hint broken?

Let’s talk about what XLOCK is supposed to do. XLOCK is a table hint that can be applied to a query to place an exclusive lock on the resources that the query accesses. This can be very dangerous because an exclusive lock on a table or partition could cause significant performance concerns.

 

In this case we will query a single row using the table’s primary key. We’ll start with a transaction and a single select statement with the XLOCK hint.

Next we’ll look at sys.dm_tran_locks to validate that the row is locked exclusively. This can be verified by observing the request_mode column and noting the “X” for exclusive.

Since this is an exclusive lock it “should” guarantee that all other attempts to access this data would be blocked. After all exclusive blocks selects, inserts, updates, and deletes. The only ways around an exclusive is with a dirty read using the NOLOCK table hint.

Finally, execute the same query without xlock from a different session and you’ll see that XLOCK is completely broken! Well not so fast…

The XLOCK table hint can be considered unreliable. This is because the SQL engine can ignore the hint if the data being accessed hasn’t changed since the oldest open transaction. Note that if both of these queries used XLOCK the second query would be blocked.

Situations like this are why I don’t recommend using hints; but, like all things in the database world: It depends.

What if there’s a business need to have an exclusive lock with a SELECT?

First, I’d ask why is the XLOCK needed. If it’s architectural how did we get here? Bad design can lead to needs for bad practice.

If the XLOCK is truly needed then combining it with PAGLOCK will block SELECT statements on the row. This is because the IX (intent lock) for the page on the second query will be blocked by the X lock on the page from the first query.

MySQL: MySQL String Functions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

MySQL String Functions

Syntax	Description	Function
1 2 SELECT CONCAT('Hello' , 'World') -- Result: 'HelloWorld'	Returns text strings concatenated	CONCAT
1 2 SELECT INSTR('hello' , 'e') -- Result: 2	Returns the location of a substring in a string.	INSTR
1 2 SELECT LENGTH('hello') -- Result: 5	Returns the number of characters of the specified string expression.	LENGTH
1 2 SELECT RTRIM(' hello    ') -- Result: ' hello'	Returns a character string after truncating all trailing blanks.	RTRIM
1 2 SELECT LTRIM('  hello    ') -- Result: 'hello    '	Returns a character expression after it removes leading blanks.	LTRIM
1 2 SELECT REPLACE('hello' , 'e' , '$') -- Result: 'h$llo'	Replaces all occurrences of a specified string value with another string value.	REPLACE
1 2 SELECT REVERSE('hello') -- Result: 'olleh'	Returns the reverse order of a string value.	REVERSE
1 2 SELECT SUBSTR('hello' , 2,3) -- Result: 'ell'	Returns part of a text.	SUBSTR
1 2 SELECT LOWER('HELLO') -- Result: 'hello'	Returns a character expression after converting uppercase character data to lowercase.	LOWER
1 2 SELECT UPPER('hello') -- Result: 'HELLO'	Returns a character expression with lowercase character data converted to uppercase.	UPPER
1 2 SELECT SELECT CONCAT('hello', space(3), 'world') -- Result: 'hello   world'	Returns a string consisting of N spaces	SPACE

MySQL Date Functions

Syntax	Description	Function
1 2 3 4 5 6 SELECT DATE_ADD('2008-01-02', INTERVAL 1 DAY); -- Result : 2008-01-03 SELECT DATE_ADD('2008-01-02', INTERVAL 1 WEEK); -- Result : 2008-01-09 SELECT DATE_ADD('2008-01-02', INTERVAL 1 MONTH); -- Result : 2008-02-02	Returns a specified date with additional time values.	DATE_ADD
1 2 SELECT DAYOFMONTH('2015-08-30') -- Result : 30	Returns an integer representing the day (day of the month) of the specified date.	DAYOFMONTH
1 2 SELECT LAST_DAY('2015-08-02') -- Result : 2015-08-31	Returns a date representing the last day of the month for specified date.	LAST_DAY
1 2 SELECT DATEDIFF('2010-04-01', '2010-03-01') -- Result : 31	Returns the difference between two days, expressed as a value in days.	DATEDIFF
1 2 SELECT PERIOD_DIFF(201005, 201003) -- Result : 2	returns the number of months between two periods.	PREIOD_DIFF
1 2 SELECT SYSDATE -- Result: (current date)	Returns the current database system date. This value is derived from the operating system of the computer on which the instance of MySQL is running.	SYSDATE()

MySQL Numeric Functions

Syntax	Description	Function
1 2 SELECT FLOOR(59.9) -- Result: 59	Returns an integer that is less than or equal to the specified numeric expression.	FLOOR
1 2 SELECT CEIL(59.1) -- Result: 60	Returns an integer that is greater than, or equal to, the specified numeric expression.	CEIL
1 2 3 4 5 SELECT ROUND(59.9) -- Result: 60   SELECT ROUND(59.1) -- Result: 59	Returns a numeric value, rounded to the specified length or precision.	ROUND

MySQL Conversion Functions

Syntax	Description	Function
1 2 SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d') -- 2015-09-16	Converts a a date into a string	DATE_FORMAT
1 2 SELECT FORMAT(1003423 , 3) -- Result : 1,003,423.000	Converts a number into a string	FORMAT
1 2 SELECT CONVERT('11', UNSIGNED INTEGER) -- Result 11	Used to convert one datatype into another, may be used to convert a string into a number	CONVERT

MySQL NULL-Related Functions

Syntax	Description	Function
1 2 SELECT IFNULL(NULL, 'Hello') -- Hello	Accepts two arguments and returns the first if its not NULL	IFNULL