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.

SITUATION:
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

PROBLEM:
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 =
pr_persona_ix
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
pr_persona_ix.
When I run the same query MySQL doesn't recognise the index name of
pr_persona_ix.

PROBLEM SUMMARY:
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?

Regards,
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.