469,338 Members | 8,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

data type matching / composite key / index name

I have a problem that has raised a couple of questions.

I have a table called pr_persona that has a composite key comprising
pr_persona_db CHAR(2)
pr_persona_id INT(11) auto increment

On a separate table, rv_reservation, I have a foreign key that links to
pr_persona as follows
rv_reservation.rv_agent_id VARCHAR(13)

I'm running MySQL version 3.23.40

When I run a SELECT statement of the following nature
SELECT rv_reservation.*, pr_persona.pr_name
FROM rv_reservation
LEFT JOIN pr_persona ON rv_reservation.rv_agent_id =
concat(pr_persona.pr_persona_db, pr_persona.pr_persona_id)
the optimiser does not join on the index - it performs a cross join.

Attempted solution 1: Created a new field on pr_persona called pr_persona_ix
VARCHAR(13) and populated it with the concatenation of pr_persona_db,
pr_persona.pr_persona_id and defined the field as a UNIQUE index.
I then changed the query to join ON rv_reservation.rv_agent_id =
Success! - the optimiser uses the new index. However, this is not an optimal
solution because I have to populate the redundant pr_persona_ix field.

Attempted solution 2: Instead of creating the index described above, I
changed the PRIMARY index to UNIQUE and gave it an index name of
When I run the same query MySQL doesn't recognise the index name of

1. Why does the optimiser not use the PRIMARY index in the original case?
2. Why is the index name of my composite UNIQUE index not recognised?

Mike Coppinger

Resrequest Online Reservation Systems
Tel: +27-11-476 4740
Fax: +27-11-476 7235
Cell: +27-82-774 0820
Email: mi***@resrequest.com
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 2630

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Greg G | last post: by
18 posts views Thread by Thomas A. Anderson | last post: by
13 posts views Thread by Baihao Yuan | last post: by
5 posts views Thread by WombatDeath | last post: by
3 posts views Thread by meter | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.