Mysql – Indexing the difference between two columns in MySQL

indexMySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following:

SELECT pk_column, column_x, column_y, column_x-column_y 
FROM table_a 
ORDER BY column_x-column_y DESC

This particular query takes about 2 seconds, however this ORDER BY seems to create a fairly large bottleneck in more complex queries.

Best Answer

You could try MariaDB which supports virtual columns.

Indexes are partially supported. Virtual columns do not support primary keys and indexes can only be based on PERSISTENT virtual columns. PERSISTENT columns can be part of a foreign key and can be referenced by foreign keys, but ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL are not allowed.

You could simulate this through your INSERTs or via TRIGGERs (better IMHO) and index the simulated virtual column. Then test to see does that resolve your issue (or at least improve it).

[EDIT in response to the OP's comment]

Yes, indeed, I thought that MariaDB would be a "bridge too far". I really included it for completeness. MariaDB is a drop-in replacement up and including 5.5 (in MariaDB terms). AFAICS, the 10 series is still compatible (it's like MySQL with bits added, but the divergence will only widen in the future AIUI.

The advantage of using a persisten column is that the persistent column can be indexed whereas the virtual one can't according to the docco that I quoted.

Has the idea of "simulated" virtual columns worked for you?

Related Solutions

Mysql – Optimizing ORDER BY for simple MySQL query

The EXPLAIN SELECT you posted definitely seems counter-intuitive.

If your query included WHERE s.id = ... then the query plan you're seeing might make a little bit more sense, but I'm assuming you're not.

It looks like the optimizer is getting distracted by the facts that supplier is a smaller table and that the supplier_id index in the po table can be used as a covering index... and with those facts in hand, it's overlooking the seemingly-obvious fact that the tables should be read in the opposite order than the one it chooses.

Here are two alternatives.

-- use the STRAIGHT_JOIN directive to insist that the optimizer process the tables in only the listed order:

SELECT STRAIGHT_JOIN * FROM `po` 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

-- use the FORCE KEY index hint to direct the optimizer to prefer the primary key of the po table:

SELECT * FROM `po` FORCE KEY (PRIMARY) 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

The first option is probably the better option, since FORCE KEY, in spite of the name, is still only a "hint" that the optimizer can choose to ignore, while STRAIGHT_JOIN genuinely does force the hand of the optimizer to join the tables in the order they're listed.

MySQL Performance – Resolving Slow Queries with Self Joins

I found that MySQL has string extraction functions like ExtractValue and SUBSTRING_INDEX which work inside WHERE and ORDER BY clauses. So I replaced the cells tables with a rows table which contains a tab delimited string, and I use SUBSTRING_INDEX and CAST to get SQL access to the individual cells.

Best Answer

Related Solutions

Mysql – Optimizing ORDER BY for simple MySQL query

MySQL Performance – Resolving Slow Queries with Self Joins

Related Question