What's the reason for using double inner join in this SQL statement?

It might help to rewrite the query like this, so it is obvious that the 2 joins are different, i.e. the joins are to different subsets (of the same table):

FROM   maintable 
       INNER JOIN secondarytable 
               ON maintable.id1 = secondarytable.a_id1 
       INNER JOIN table1 
               ON secondarytable.id2 = table1.id3 
              AND table1.type = '0' 
       INNER JOIN table1 Table1Alias 
               ON secondarytable.id2 = Table1Alias.id3 
              AND Table1Alias.columna = 'conn' 
       INNER JOIN
       ...
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09' 
       AND secondarytable.type456 = 'cate' 

Looking at the where clause, the row being pointed to by table1 requires the column type to = '0' and the row being pointed to by table1alias requires the column columna to = 'conn'.

Perhaps there are multiple rows on table1 for the same id3?

Without seeing the table structure - the approach could be to make use of a smaller non-covering index and then joining into the table on a larger covering index to get remainder of rows to avoid a 'Key Lookup' operation and to avoid modifying existing indexes (or if you cannot modify the indexes)