https://support.na.sage.com/selfservice/viewContent.do?externalId=80899&sliceId=1

Error: "SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'

Products

Sage 500 ERP

Country

North America

Description

Error: "SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Error: "SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Error: "SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Error message(s) when attempting to login to Sage 500

Disclaimer

Backup Warning

Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.

Operating System Warning

This solution requires advanced knowledge of your computer's operating system. Contact your system administrator for assistance. Modifying your Windows Registry incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows Registry. Always create a backup of your data before proceeding with advanced solutions.

Resolution

• Log into SQL Server using an 'sa' or equivalent account
• Execute the following in a new query window

sp_configure 'show advanced options', 1
GO
RECONFIGURE;

• Then execute the following

sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;

• Close and reopen the Sage 500 application and proceed to login.

xp_cmdshell - Procedure expects parameter 'command_string' of type 'varchar'

https://www.sqlservercentral.com/forums/topic/xp_cmdshell-procedure-expects-parameter-command_string-of-type-varchar


Here are the contents of @Cmd (varchar(max)):
bcp "SELECT [Data] FROM myserver..dbo.PEC_Citrus_RptData where [Data] is not null order by [Data]" queryout "C:\Documents and Settings\All Users\Documents\Shared\OutputFile.txt" -c -t -T -S "MYMACHINE\MYINSTANCE"
Here's the code:
EXEC master..xp_cmdshell @Cmd ;
Here is the error:
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
?



You can't use varchar(max) with xp_cmdshell. Found that out the hard way. Try a finite number (I usually just go with either 1000 or 8000).

Enabling xp_cmdshell in SQL Server

https://www.mssqltips.com/sqlservertip/1020/enabling-xpcmdshell-in-sql-server/

If you have sysadmin privileges and don't enable xp_cmdshell and you issue a command such as the following to get a directory listing of the C: drive:

xp_cmdshell 'dir c:\'

you get the following error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

If you don't have sysadmin privileges and try to run xp_cmdshell whether it is enable or not you get this error message:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Another error you may get if you try to enable xp_cmdshell using sp_configure when advanced options is not set is the following error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 2]
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

So in order to use xp_cmdshell whether you are a sysadmin or a regular user you need to first enable the use of xp_cmdshell.

Enable xp_cmdshell with sp_configure

The following code with enable xp_cmdshell using sp_configure. You need to issue the RECONFIGURE command after each of these settings for it to take effect.

-- this turns on advanced options and is needed to configure xp_cmdshell
sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE

Disable xp_cmdshell with sp_configure

The following code with disable xp_cmdshell using sp_configure:

-- this turns on advanced options and is needed to configure xp_cmdshell
sp_configure 'show advanced options', '1'
RECONFIGURE
-- this disables xp_cmdshell
sp_configure 'xp_cmdshell', '0' 
RECONFIGURE

Enable or Disable xp_cmdshell with SSMS Facets

From within SSMS, right click on the instance name and select Facets.
The in the Facet drowdown, change to Server Security as shown below. 
You can then change the setting for XpCmdShellEnabled as needed to either True or False. After changing the value, click OK to save the setting and the change will take effect immediately.  There is not a need to enable show advanced options or use reconfigure, the GUI takes care of this automatically.

Granting Access to xp_cmdshell

Let's say we have a user that is not a sysadmin, but is a user of the master database and we want to grant access to run xp_cmdshell.

-- add user test to the master database
USE [master]
GO
CREATE USER [test] FOR LOGIN [test]
GO

-- grant execute access to xp_cmdshell
GRANT EXEC ON xp_cmdshell TO [test]

We get this error message:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

There is not a need to give a user sysadmin permissions or elevated permissions to run xp_cmdshell.  To do so you can create a proxy account as shown in this tip Creating a SQL Server proxy account to run xp_cmdshell.

how can I Update top 100 records in sql server

https://stackoverflow.com/questions/1198364/how-can-i-update-top-100-records-in-sql-server

Note, the parentheses are required for UPDATE statements:

update top (100) table1 set field1 = 1

How to get all installed programs on your machine

https://kencenerelli.wordpress.com/2017/11/25/list-installed-programs-on-windows-10/

1. Open a Command Prompt in Administrator Mode
2. At the prompt, type wmic and press Enter
3. The prompt changes to wmic:root\cliInstalled01
4. Type /output:C:\Temp\InstalledPrograms.txt product get name,version and press Enter
5. Note: Depending on the number of programs installed, it may take a few seconds for the list to be created. Once the list is complete, you are returned to the wmic:root\cli prompt.
6. Close the Command Prompt
   
   Here is a simple trick to list all of the installed programs on your Windows 10 computer.
   1. Launch the Command Prompt by typing Command Prompt into the search box on the menu bar
   2. Right-click the app returned and select Run As Administrator
   3. At the prompt, specify wmic and press Enter
   4. The prompt changes to wmic:root\cli
   5. Specify /output:C:\InstalledPrograms.txt product get name,version and press EnterNote: Depending on the number of programs installed, it may take a few seconds for the list to be created. Once the list is complete, you are returned to the wmic:root\cli prompt.
   6. Close the Command Prompt
   7. Open the InstalledProgramsList.txt file in Notepad++. The Name and Version of every program installed on your computer are presented in table format.

   
   

https://stackoverflow.com/questions/3664393/how-to-group-mysql-rows-with-same-column-value-into-one-row

How to group mysql rows with same column value into one row?

Use GROUP_CONCAT() like this:

 SELECT k.id, GROUP_CONCAT(d.value)
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

Also, you may need to do ORDER BY d.name to get exact order of values as you want. Like this:

 SELECT k.id, GROUP_CONCAT(d.value ORDER BY d.name separator ' ')
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists

Insert into a MySQL table or update if exists

Use INSERT ... ON DUPLICATE KEY UPDATE
QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

 

 

When using batch insert use the following syntax:

INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
    name = VALUES (name),
    ...

 

 

MySql Query Replace NULL with Empty String in Select

https://stackoverflow.com/questions/9560723/mysql-query-replace-null-with-empty-string-in-select

select if(prereq IS NULL ," ",prereq ) from test
select IFNULL(prereq,"") from test
select coalesce(prereq, '') from test

https://www.delphitools.info/2013/11/29/flush-windows-file-cache/

Flush Windows File Cache

By Eric Grange / November 29, 2013

Here is a small utility I made whose purpose is to flush (empty) the Windows file cache. It can also flush and purge memory working sets, standby and modified lists.
It can be useful to reduce memory usage of a particular VM (on a host with dynamic memory) or for testing and bench-marking purposes.

The utility was compiled with Delphi XE and comes as a simple command-line executable with just two options

• by default (no option) it will flush the file cache working set and empty the memory working set, this is usually very fast and has limited impact.
• “full” will also flush the modified list and purge memory standby list, this can be slow and may slow down the machine for a while after it, as the OS will reload in memory what it really needs.
• “help” will display a small help.

You can download it here: FlushFileCache.zip (13 kB)
Note that it relies on the undocumented NtSetSystemInformation function. The source code will be available on a github repository as soon as I clean up the proprietary bits.
FWIW, if you have some memory-frugal service to run, it’s entirely feasible to use this utility on a Windows 2008 R2 server and stay under 128 MB RAM usage. Most of the unused OS services will be swapped out, leaving only core services in RAM.

MariaDB Explain

https://mariadb.com/kb/en/explain/

EXPLAIN

Syntax

EXPLAIN tbl_name

Or

EXPLAIN [EXTENDED | PARTITIONS] 
  {SELECT select_options | UPDATE update_options | DELETE delete_options}

Contents

1. Syntax
2. Description
1. Columns in EXPLAIN ... SELECT
1. "Select_type" Column
2. "Type" Column
3. "Extra" Column
2. EXPLAIN EXTENDED
3. Examples
1. Example of ref_or_null Optimization
4. See Also

Description

The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MariaDB executes a SELECT (as well as UPDATE and DELETE since MariaDB 10.0.5) statement:

• 'EXPLAIN tbl_name' is synonymous with 'DESCRIBE tbl_name' or 'SHOW COLUMNS FROM tbl_name'.
• When you precede a SELECT statement (or, since MariaDB 10.0.5, an UPDATE or a DELETE as well) with the keyword EXPLAIN, MariaDB displays information from the optimizer about the query execution plan. That is, MariaDB explains how it would process the SELECT, UPDATE or DELETE, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.
  
• EXPLAIN PARTITIONS has been available since MySQL 5.1.5. It is useful only when examining queries involving partitioned tables.
  For details, see Partition pruning and selection.
• ANALYZE statement, which performs the query as well as producing EXPLAIN output, and provides actual as well as estimated statistics, has been available from MariaDB 10.1.0.
• Since MariaDB 10.0.5, it has been possible to have EXPLAIN output printed in the slow query log. See EXPLAIN in the Slow Query Log for details.

Since MariaDB 10.0, SHOW EXPLAIN shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN.
Since MariaDB 10.1, the ANALYZE statement runs a statement and returns information about its execution plan. It also shows additional columns, to check how much the optimizer's estimation about filtering and found rows are close to reality.
There is an online EXPLAIN Analyzer that you can use to share EXPLAIN and EXPLAIN EXTENDED output with others.

EXPLAIN can acquire metadata locks in the same way that SELECT does, as it needs to know table metadata and, sometimes, data as well.

Columns in EXPLAIN ... SELECT

Column name	Description
id	Sequence number that shows in which order tables are joined.
select_type	What kind of SELECT the table comes from.
table	Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type	How rows are found from the table (join type).
possible_keys	keys in table that could be used to find rows in the table
key	The name of the key that is used to retrieve rows. NULL is no key was used.
key_len	How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref	The reference that is used as the key value.
rows	An estimate of how many rows we will find in the table for each key lookup.
Extra	Extra information about this join.

Here are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT:

"Select_type" Column

The select_type column can have the following values:

Value	Description
DEPENDENT SUBQUERY	The SUBQUERY is DEPENDENT.
DEPENDENT UNION	The UNION is DEPENDENT.
DERIVED	The SELECT is DERIVED from the PRIMARY.
MATERIALIZED	The SUBQUERY is MATERIALIZED.
PRIMARY	The SELECT is a PRIMARY one.
SIMPLE	The SELECT is a SIMPLE one.
SUBQUERY	The SELECT is a SUBQUERY of the PRIMARY.
UNCACHEABLE SUBQUERY	The SUBQUERY is UNCACHEABLE.
UNCACHEABLE UNION	The UNION is UNCACHEABLE.
UNION	The SELECT is a UNION of the PRIMARY.
UNION RESULT	The result of the UNION.
LATERAL DERIVED	The SELECT uses a Lateral Derived optimization

"Type" Column

This column contains information on how the table is accessed.

Value	Description
ALL	A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows.
const	There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants.
eq_ref	A unique index is used to find the rows. This is the best possible plan to find the row.
fulltext	A fulltext index is used to access the rows.
index_merge	A 'range' access is done for for several index and the found rows are merged. The key column shows which keys are used.
index_subquery	This is similar as ref, but used for sub queries that are transformed to key lookups.
index	A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table.
range	The table will be accessed with a key over one or more value ranges.
ref_or_null	Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. This happens usually with sub queries.
ref	A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn't match many rows.
system	The table has 0 or 1 rows.
unique_subquery	This is similar as eq_ref, but used for sub queries that are transformed to key lookups

"Extra" Column

This column consists of one or more of the following values, separated by ';'
Note that some of these values are detected after the optimization phase.
The optimization phase can do the following changes to the WHERE clause:

• Add the expressions from the ON and USING clauses to the WHERE clause.
• Constant propagation: If there is column=constant, replace all column instances with this constant.
• Replace all columns from 'const' tables with their values.
• Remove the used key columns from the WHERE (as this will be tested as part of the key lookup).
• Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'.
• Replace columns with other columns that has identical values: Example: WHERE a=b and a=c may be treated as 'WHERE a=b and a=c and b=c'.
• Add extra conditions to detect impossible row conditions earlier. This happens mainly with OUTER JOIN where we in some cases add detection of NULL values in the WHERE (Part of 'Not exists' optimization). This can cause an unexpected 'Using where' in the Extra column.
• For each table level we remove expressions that have already been tested when we read the previous row. Example: When joining tables t1 with t2 using the following WHERE 't1.a=1 and t1.a=t2.b', we don't have to test 't1.a=1' when checking rows in t2 as we already know that this expression is true.

Value	Description
const row not found	The table was a system table (a table with should exactly one row), but no row was found.
Distinct	If distinct optimization (remove duplicates) was used. This is marked only for the last table in the SELECT.
Full scan on NULL key	The table is a part of the sub query and if the value that is used to match the sub query will be NULL, we will do a full table scan.
Impossible HAVING	The used HAVING clause is always false so the SELECT will return no rows.
Impossible WHERE noticed after reading const tables.	The used WHERE clause is always false so the SELECT will return no rows. This case was detected after we had read all 'const' tables and used the column values as constant in the WHERE clause. For example: WHERE const_column=5 and const_column had a value of 4.
Impossible WHERE	The used WHERE clause is always false so the SELECT will return no rows. For example: WHERE 1=2
No matching min/max row	During early optimization of MIN()/MAX() values it was detected that no row could match the WHERE clause. The MIN()/MAX() function will return NULL.
no matching row in const table	The table was a const table (a table with only one possible matching row), but no row was found.
No tables used	The SELECT was a sub query that did not use any tables. For example a there was no FROM clause or a FROM DUAL clause.
Not exists	Stop searching after more row if we find one single matching row. This optimization is used with LEFT JOIN where one is explicitly searching for rows that doesn't exists in the LEFT JOIN TABLE. Example: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. As t2.not_null_column can only be NULL if there was no matching row for on condition, we can stop searching if we find a single matching row.
Open_frm_only	For information_schema tables. Only the frm (table definition file was opened) was opened for each matching row.
Open_full_table	For information_schema tables. A full table open for each matching row is done to retrieve the requested information. (Slow)
Open_trigger_only	For information_schema tables. Only the trigger file definition was opened for each matching row.
Range checked for each record (index map: ...)	This only happens when there was no good default index to use but there may some index that could be used when we can treat all columns from previous table as constants. For each row combination the optimizer will decide which index to use (if any) to fetch a row from this table. This is not fast, but faster than a full table scan that is the only other choice. The index map is a bitmask that shows which index are considered for each row condition.
Scanned 0/1/all databases	For information_schema tables. Shows how many times we had to do a directory scan.
Select tables optimized away	All tables in the join was optimized away. This happens when we are only using COUNT(*), MIN() and MAX() functions in the SELECT and we where able to replace all of these with constants.
Skip_open_table	For information_schema tables. The queried table didn't need to be opened.
unique row not found	The table was detected to be a const table (a table with only one possible matching row) during the early optimization phase, but no row was found.
Using filesort	Filesort is needed to resolve the query. This means an extra phase where we first collect all columns to sort, sort them with a disk based merge sort and then use the sorted set to retrieve the rows in sorted order. If the column set is small, we store all the columns in the sort file to not have to go to the database to retrieve them again.
Using index	Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.
Using index condition	Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the index level.
Using index condition(BKA)	Like 'Using index condition' but in addition we use batch key access to retrieve rows.
Using index for group-by	The index is being used to resolve a GROUP BY or DISTINCT query. The rows are not read. This is very efficient if the table has a lot of identical index entries as duplicates are quickly jumped over.
Using intersect(...)	For index_merge joins. Shows which index are part of the intersect.
Using join buffer	We store previous row combinations in a row buffer to be able to match each row against all of the rows combinations in the join buffer at one go.
Using sort_union(...)	For index_merge joins. Shows which index are part of the union.
Using temporary	A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.
Using where	A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong!
Using where with pushed condition	Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the row level.
Using buffer	The UPDATE statement will first buffer the rows, and then run the updates, rather than do updates on the fly. See Using Buffer UPDATE Algorithm for a detailed explanation.

EXPLAIN EXTENDED

The EXTENDED keyword adds another column, filtered, to the output. This is a percentage estimate of the table rows that will be filtered by the condition.
An EXPLAIN EXTENDED will always throw a warning, as it adds extra Message information to a subsequent SHOW WARNINGS statement. This includes what the SELECT query would look like after optimizing and rewriting rules are applied and how the optimizer qualifies columns and tables.

Examples

As synonym for DESCRIBE or SHOW COLUMNS FROM:

DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

A simple set of examples to see how EXPLAIN can identify poor index usage:

CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `position` varchar(25) NOT NULL,
  `home_address` varchar(50) NOT NULL,
  `home_phone` varchar(12) NOT NULL,
  `employee_code` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `first_name` (`first_name`,`last_name`)
) ENGINE=Aria;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');

SHOW INDEXES FROM employees_example;
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees_example |          0 | PRIMARY       |            1 | id            | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          0 | employee_code |            1 | employee_code | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            1 | first_name    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            2 | last_name     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SELECT on a primary key:

EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table             | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | employees_example | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+

The type is const, which means that only one possible result could be returned. Now, returning the same record but searching by their phone number:

EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employees_example | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

Here, the type is All, which means no index could be used. Looking at the rows count, a full table scan (all six rows) had to be performed in order to retrieve the record. If it's a requirement to search by phone number, an index will have to be created.
SHOW EXPLAIN example:

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

Example of ref_or_null Optimization

SELECT * FROM table_name
  WHERE key_column=expr OR key_column IS NULL;

ref_or_null is something that often happens when you use subqueries with NOT IN as then one has to do an extra check for NULL values if the first value didn't have a matching row.

See Also

• SHOW EXPLAIN