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: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
