In the last episode (May 23), Ruben I Safir said:
> Greeting Monty from Brooklyn USA.
>
> I wanted to select on an index which is concated on several rows.
>
> I have a table that more or less looks like this:
> mysql> show COLUMNS from TRANSACT;
> +-------------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+-------+
> | TRANSNO | varchar(15) | YES | | NULL | |
> | STNO | varchar(5) | | MUL | | |
> | CHARTNO | varchar(6) | | MUL | 999999 | |
> | ADACODE1 | varchar(5) | | | | |
> | TOOTH1 | varchar(47) | | | | |
> | SECTOR1 | char(2) | | | | |
snip
> And the Indexs are as Follows:
>
> +----------+------------+----------+--------------+-------------+-----------+-------------+----------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
> Cardinality | Sub_part |
>
> +----------+------------+----------+--------------+-------------+-----------+-------------+----------+
> | TRANSACT | 1 | CHORE1 | 1 | CHARTNO | A |
> NULL | NULL |
> | TRANSACT | 1 | CHORE1 | 2 | ADACODE1 | A |
> NULL | NULL |
> | TRANSACT | 1 | CHORE1 | 3 | TOOTH1 | A |
> NULL | NULL |
> | TRANSACT | 1 | CHORE1 | 4 | SECTOR1 | A |
> NULL | NULL |
> | TRANSACT | 1 | CHORE1 | 5 | SURFACE1 | A |
> NULL | NULL |
snip
> When I select useing CONCAT like this:
>
> mysql> explain select CDTDATE, CHARTNO, ADACODE1, TOOTH1, SECTOR1,
> SURFACE1, STATUS1, CONCAT(CHARTNO, ADACODE1, TOOTH1, SECTOR1, SURFACE1)
> as JOB from TRANSACT where CONCAT(CHARTNO, ADACODE1, TOOTH1, SECTOR1,
> SURFACE1) = '43134005211' ORDER BY CHARTNO, ADACODE1, TOOTH1, SECTOR1,
> SURFACE1;
> +----------+------+---------------+------+---------+------+-------+------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----------+------+---------------+------+---------+------+-------+------------+
> | TRANSACT | ALL | NULL | NULL | NULL | NULL | 50593 | where used |
> +----------+------+---------------+------+---------+------+-------+------------+
>
> No columns are used. Even if I select the fields with
> WHERE COL='string' AND ....
>
> The indexs CHORE1 and CHORE2 still fail to be used. Nothing ever shows
> up with a ORDER BY clause, which seems to be against the documentation.
> Is there a way to select on the Index directly and by pass the table?
The use of CONCAT forced mysql to ignore the indexes. If you rewrote
your WHERE clause to read "WHERE CHARTNO='431340' and ADACODE1='00521'
AND TOOTH1='1'", it should be able to use the CHORE1 index. In
general, it's very hard for any database program to use indexes when
the fields aren't directly compared.
--
Dan Nelson
dnelson@stripped
