List:General Discussion« Previous MessageNext Message »
From:Jay J Date:December 5 1999 4:54am
Subject:Re: Index names in Where Clauses
View as plain text  
----- Original Message -----
From: "pina-netc" <pina@stripped>
To: <mysql@stripped>
Sent: Saturday, December 04, 1999 10:23 PM
Subject: Index names in Where Clauses


>        Does someone know if there is any way to make a select
> statment with an index name in the where clause, composed
> with more than one columns.
>
>Best regards, and thanks a lot.

The manual describes multi-column _indexes_, but I'm guessing that maybe
isn't what you want.

Are you trying to ORDER BY the combination of two columns (see below)?

Just guessing,

-Jay J

mysql> CREATE table foo ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, col_a
CHAR(10), col_b CHAR(10));
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO foo VALUES (null, 'a', 'a'), (null, 'a', 'b'), (null,
'b', 'a'), (null, 'b', 'b');
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain foo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned |      | PRI | 0       | auto_increment |
| col_a | char(10)         | YES  |     | NULL    |                |
| col_b | char(10)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql> SELECT id, col_a, col_b, CONCAT(col_a, col_b) as OB FROM foo ORDER BY
OB desc;
+----+-------+-------+------+
| id | col_a | col_b | OB   |
+----+-------+-------+------+
|  4 | b     | b     | bb   |
|  3 | b     | a     | ba   |
|  2 | a     | b     | ab   |
|  1 | a     | a     | aa   |
+----+-------+-------+------+
4 rows in set (0.20 sec)

mysql> SELECT id, col_a, col_b, CONCAT(col_a, col_b) as OB FROM foo ORDER BY
OB ASC;
+----+-------+-------+------+
| id | col_a | col_b | OB   |
+----+-------+-------+------+
|  1 | a     | a     | aa   |
|  2 | a     | b     | ab   |
|  3 | b     | a     | ba   |
|  4 | b     | b     | bb   |
+----+-------+-------+------+
4 rows in set (0.01 sec)


Thread
Index names in Where Clausespina-netc5 Dec
  • Re: Index names in Where ClausesJay J5 Dec