Fun With Functional Indexes

    Functional indexes are way to build indexes on values derived from rather than values in columns, or parts there of.  Pretend your very price sensitive customers want to buy products where the combined price of the product and the shipping on that product are below a certain amount.

CREATE index cost_and_shipping ON products((cost + shipping));

    Please note the extra () around the calculation on the value to be indexed.  You will need them to create the functional index.  So lets run a quick query and then look at the output from EXPLAIN to determine if the new index was used;

select *

from products

where cost + shipping = 3; +----+------+----------+----------+ | id | cost | shipping | name | +----+------+----------+----------+ | 2 | 2 | 1 | Dohickey | | 3 | 2 | 1 | Whatsits | +----+------+----------+----------+ 2 rows in set (0.0009 sec)

EXPLAIN FORMAT=TREE

select *

from products

where cost + shipping = 3\G *************************** 1. row *************************** EXPLAIN: -> Index lookup on products using cost_and_shipping ((cost + shipping)=3) (cost=0.70 rows=2) 1 row in set (0.0011 sec)

    And yes, the optimizer can use the cost_and_shipping index.

However, please beware that you have work with the index as it is designed. In this case it works for cost plus shipping, not shipping plus cost. Mix the order and your query will not be able to utilize the index.

EXPLAIN FORMAT=TREE

select *

from products

where shipping + cost= 3\G

*************************** 1. row ***************************

EXPLAIN: -> Filter: ((products.shipping + products.cost) = 3)  (cost=0.65 rows=4)

    -> Table scan on products  (cost=0.65 rows=4)

1 row in set (0.0013 sec)

    So, please take advantage of MySQL's functional index ability but make sure you use your indexes as you define them.

Don't Forget to Check Your Accounts

    The start of fall in the northern hemisphere is a time for many of use to prepare for the approaching winter. Part of that is clearing away and storing summer things and pulling out of storage the winter things. And one of those things I suggest to you do is look at the accounts on your MySQL instances.

    It is very easy, especially with multiple folks creating and alter doing user administration, to have redundant accounts.  You may also have  unneeded accounts.  Or you can have accounts that expired and nobody asked to have them reset.  And you may have accounts that you are not sure who or what uses them.

Start with this simple query to get a first look at your user data.

use mysql;
select User, Host, password_expired, account_locked, User_attributes from user;  

    Examine which accounts are expired and locked.  Do you know why they are in this state? 

    Next look at the User and Host data sorted by User. 

select User, Host from user order by User, Host;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| Foo              | %         |
| bar              | localhost |
| bill             | localhost |
| davetest         | localhost |
| demo             | localhost |
| demo2            | localhost |
| dstokes          | %         |
| dstokes          | localhost |
| foo              | localhost |
| foobar           | localhost |
| jack             | localhost |
| mary             | localhost |
| mike             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | %         |
| root             | localhost |
+------------------+-----------+

    In this case notice that there are two separate and distinct accounts where the User column is 'dstokes'.  Someone might have wanted one account or maybe there are two different users to justify the account.  Always look at the account as the pair of the User and Host columns.  If this was supposed to be one account, save yourself some future headaches and merge the accounts.  Remember the MySQL authentication system is a wee bit promisors and lets the first matching account into the server.  This is why dstokes@localhost can not read the data that is granted to dstokes@%! 

    And when you discover that 'dstokes'@'%' is Dave Stokes and dstokes@'localhost is Dilbert Stokes please annotate this information.

ALTER USER 'dstokes'@'%' COMMENT 'This is remote account for Dave Stokes x1234';

ALTER USER 'dstokes'@'localhost' COMMENT 'Dilbert Stokes debug account';