I choose this:
1. SELECT x.id, y.id, z.id, w.id, s.id FROM x, z, y, w, s ORDER BY x.id DESC, y.id DESC, z.id DESC, w.id DESC, s.id DESC limit 1
2. or through SELECT MAX(x.id) ...

The problem is that the sample without DESC takes 0.001 seconds, but with DESC first version is 35 seconds, and the second 15 seconds ...

And here I thought that I needed help. I looked in the direction of JOIN , but did not understand how to attach it here. I do not need to compare anything, just get the latest entries from each of the tables.

  • Are there indices for fields that you combine in where? A DESC - of course it will "slow down", because scans tables coming - Chubatiy
  • There is an index on the fields id. And there is no where, so maybe it slows down, because there is no search key, and search criteria ... Because, how can I set the search criterion by going to where, if I need to select without criteria, just the last entries? WHERE max (id)? - dima buhayov

1 answer 1

@Chubatiy thanks, pushed to the thought.

Perhaps this is from the category of perversions, but as an option it should exist.

 SELECT x.id, y.id FROM x, y WHERE x.id=( SELECT max(id) FROM x ), y.id=( SELECT max(id) FROM y) LIMIT 1

This option, through subqueries, was executed for 0.0028 (I only have 8 tables, with 5 fields in each)

  • four
    can then select (select max (a.id) from a), (select max (bd) from b) ... And you don’t need the general from with listing all the tables because it is he who is slowing down trying to multiply what is contained - Mike
  • I tried this option, but it rolls only if SELECT (SELECT MAX(b.id) FROM b) = one column in the internal select, if for one table to write in this style several columns, then the query will be kilometer long. If multiple beats error # 1241 - Operand should contain 1 column (s) - that is, only one column is allowed. Although performed much faster, 0.0010 - dima buhayov
  • Yes, there is such a thing. then select * from (select max(id) a,min(id) b from b) A, (select ... from b) B ... main thing is that all queries return exactly on one line. - Mike
  • and you choose something else besides x.id, y.id? because if not, then there is no point in such a sample — it is much easier to take SELECT MAX(x.id), MAX(y.id) FROM x, y - PashaPash ♦
  • @PashaPash so in the original question of the vehicle it was. only in this case, the optimizer will make a full scan of both tables, multiply the results and only after that it will select the maximum values ​​from the result - Mike