List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 23 2000 7:46pm
Subject:Re: Selecting on An Index
View as plain text  
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
Thread
Selecting on An IndexRuben I Safir23 May
  • Re: Selecting on An IndexDan Nelson23 May
  • Re: Selecting on An IndexRuben I Safir24 May