CODEWITHSUNDEEP
mysqlsql
juancar
juancar

Reputation: 327

Select rows where values of two columns are in array

I have a long table with stock prices that, among other fields, includes the following:

|    Date    |    exchange_code    |    ticker    |    price    |
|------------|---------------------|--------------|-------------|
|2020 -01-01 |         US          |    GOOG      |    XXXXX    |

I would like to know which is the most efficient way to select rows where pairs (exchange_code and ticker) are included in a given array.

The only idea I have is to add another column like exchange_ticker and then use a query like

SELECT * FROM mytable 
WHERE exchange_ticker IN (X,Y,Z);

Also, as the table is quite long, I would add an index over this column.

I'm sure there must be a better way... any idea?

Thanks!

Upvotes: 0

Views: 1163

Answers (2)

Strawberry
Strawberry

Reputation: 33935

GL's idea is a nice one but (at least in older versions of MySQL) it's not going to help much...

mysql> EXPLAIN
    -> SELECT *
    -> FROM mytable
    -> WHERE (exchange_code, ticker) IN ( ('US','MOBI'), ('US','TESL'), ('UK','BP') );
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mytable | ALL  | NULL          | NULL | NULL    | NULL |  147 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT *
    ->   FROM mytable
    ->  WHERE (exchange_code = 'US' AND ticker = 'MOBI')
    ->     OR (exchange_code = 'US' AND ticker = 'TESL')
    ->     OR (exchange_code = 'UK' AND ticker = 'BP')
    ->     ;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mytable | ALL  | exchange_code | NULL | NULL    | NULL |  147 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.21    |
+-----------+

The second query doesn't use the index because my data set is tiny. The first query doesn't even find the index.

If I increase the size of the data set (and the cardinality of exchange_code) slightly, the difference becomes more pronounced.

mysql> EXPLAIN
    -> SELECT *
    ->   FROM mytable
    ->  WHERE (exchange_code = 'US' AND ticker = 'MOBI')
    ->     OR (exchange_code = 'US' AND ticker = 'TESL')
    ->     OR (exchange_code = 'UK' AND ticker = 'BP')
    ->     ;
+----+-------------+---------+-------+---------------+---------------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+---------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | mytable | range | exchange_code | exchange_code | 16      | NULL |   43 | Using index condition |
+----+-------------+---------+-------+---------------+---------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT *
    -> FROM mytable
    -> WHERE (exchange_code, ticker) IN ( ('US','MOBI'), ('US','TESL'), ('UK','BP') );
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mytable | ALL  | NULL          | NULL | NULL    | NULL | 2352 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can use tuples:

SELECT *
FROM mytable 
WHERE (exchange, ticker) IN ( (A, X), (B, Y), (C, Z) );

This should be able to take advantage of an index on mytable(exchange, ticker).

If the list is already in a table, you can use JOIN:

select t.*
fro mytable t join
    list l
    using (exchange, ticker);

You also want an index for this as well.

Upvotes: 2

Related Questions