0

I am trying to build a query for later inserting it in a table I'm building. I want the query to return 4 columns, which are 4 ids from different entities which relate with each other. I've got one table with the relations, but with varchars, and four tables where I already put the different occurences with an ID each:

TABLE RELATIONS:
A        B          C          D
Sony     Bravia     32"        1200€
JVC      Whatever   15cm       200€
Samsung  Galaxy     13"        500€

TABLE A:
id    name
1     Sony
2     JVC
3     Samsung

TABLE B:
id    name
1     Whatever
2     Galaxy
3     Bravia

TABLE C:
id    name
1     13"
2     15cm
3     32"

TABLE D:
id    name
1     200€
2     1200€
3     500€

Now, what I want to get with my query is:

QUERY RESULT:
A        B       C       D
1        3       3       2
2        1       2       1
3        2       1       3

Firstly, I constructed this one:

SELECT DISTINCT A.id as A, B.id as B, C.id as C, D.id as D
FROM relations
INNER JOIN A ON A.name = relations.A
INNER JOIN B ON B.name = relations.B
INNER JOIN C ON C.name = relations.C
INNER JOIN D ON D.name = relations.D

It seems correct, but it takes sooooo long (maybe hours) to complete. The table sizes are (80,65000,1900,15) for the 4 entities, and 65000 for the relations table.

If I perform just one of the joins it takes 15ms, if I perform two of them 6-7 seconds, and with 3 o 4 the time increases exponentially. I think maybe the JOIN solution might be overkill for my situation, as I only need to "translate" the strings...

I already created an index for every "name" field in the four entity tables, as well as an index for relations.a,.b,.c,.d.

Curiously, it takes almost nothing if what I do is duplicate the a,b,c,d columns in the relations table and perform 4 UPDATE queries inserting the id into the duplicate field, matching its "parent"...but I'm sure there's gotta be a better way to do that...anyone has an idea?

Many thanks! EXPLAIN result http://www.redlanemedia.com/explain.png

Martí Gascó
  • 385
  • 2
  • 11
  • 2
    As with all MySQL query optimizing questions, you start with `EXPLAIN`. – N.B. Jun 07 '13 at 12:27
  • 3
    Also, your relations table would be so much better if you saved the ID values in the A,B,C,D columns instead of the long text.. thats the whole point of joining... don't duplicate text when IDs are better / faster for joining (and typically less space) – DRapp Jun 07 '13 at 12:33
  • As you have mentioned that you have create indexes on the necessary columns. Check whether the dataType and size is same for all the columns that are being used in the join http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Meherzad Jun 07 '13 at 12:36
  • The thing is, my relations table is a "bad" table, which comes from a xls file. What I'm doing is populating a nice database, I've already populated all the entities and generated their ids, and the last step is creating a "good" relations table. This SELECT is intended to go after an INSERT to populate this new relations table – Martí Gascó Jun 07 '13 at 12:36
  • Agree with @N.B. and would further add that you should try and have indexes on each of the name columns so that your joins can use them, and you can force them to be used if your EXPLAIN suggests they are not being used. – Steph Locke Jun 07 '13 at 12:38
  • isn't `name`(string joining) joining slower than joining ids? – Jurijs Nesterovs Jun 07 '13 at 12:40
  • one better way would be make a well formated xls file, then convert it to .csv and import it to one table instead of 4... – Jurijs Nesterovs Jun 07 '13 at 12:43
  • I did that! I imported the CSV into a table (the existing relations table) and I am popullating a whole set of separate entities, one per table, and making a new relations table just with ids. It's the migration query I'm having trouble with – Martí Gascó Jun 07 '13 at 12:51
  • Added the EXPLAIN result up in the question. You'll see different table names, as I simplified those for you. – Martí Gascó Jun 07 '13 at 12:57
  • add relationship key columns to your other tables and don't forget to build index's on the keys that'll speed up your joins massively – Dave Jun 07 '13 at 12:58
  • I am trying to make a table with the relationship columns, as the 4 tables relate at the same time, so I can't create relationship key columns in the 4 tables... I've got all the involved columns indexed as well... – Martí Gascó Jun 07 '13 at 13:09

0 Answers0