maybe here can me / give hint how stuff. we're using 2 tables in mysql, both myisam, utf8. first table hold registrations objects, seconds table holds objects additional information name etc.
the match between table 1 , table2 based on code-column both tables. why not id? because table1 being filled external system, doesn't know id of object it's linked to. also, can have registrations objects not available in table 2, needs registered in table 1.
table1 - registrations: (+/- 33.000.000 rows)
+-------------+ | id | | code | | datetime | +-------------+ table2 - objects: (+/- 55.000 rows)
+-------------+ | id | | code | | name | | description | | etc. | +-------------+ both tables has indexes on columns. regular select query goes fine. creates result of 32.382.742 rows in 0.0017 seconds.
select * table1 left join table2 on table2.code = table1.code when order on columns table 1, still ok. creates result of 32.382.742 rows in 0.0179 seconds.
select * table1 left join table2 on table2.code = table1.code order table1.datetime when order on columns fom table 2, it's bad. mysql's show processlist; shows temporary table being created , didn't stop 300 seconds, killed mysql.
select * table1 left join table2 on table2.code = table1.code order table2.name also tried select table2 right join on table 1, won't work.
how solve / fast?
you using left join, dbms orders rows columns of table2 null defaults. not sure if got problem, try extending order by clause this
select * table1 left join table2 on table2.code = table1.code order table2.name, table1.datetime or (not sure if work)
select * table1 left join table2 on table2.code = table1.code order isnull(table2.name,table1.datetime) 
Comments
Post a Comment