MySQL performance - multiple queries or one inefficient query?

Question

I have three tables, each of which contains some general information and some information that is unique to the table. For example: uid , date are universal among tables, but one table may contain a type column and the other a currency . I need to query the database and get the last 20 records ( date DESC ) that were entered in all three tables. My parameters:

• Query the database once, with one large query, containing three UNION ALL clauses and pass fake values ​​for columns, IE:

   FROM ( SELECT uid, date, currency, 0, 0, 0 

  and then

   FROM ( SELECT uid, date, 0, type, 0, 0 

  That would leave me with zero fields highlighted.

• OR I can query the database three times and somehow in PHP sort through the information to get the combined last 20 messages. This would leave me with an abundance of information - 60 messages to view ( LIMIT 20 ) * 3 - and force me to force some type of extra sorting each time.

Which option is better / any alternative ideas?

Thanks.

Answer 1

These two options are similar to each other than to sound.

When you execute a single large query with UNION s, MySQL will still execute three separate queries, just as you propose to do in your alternative plan, and then combine them into one result.

So, you can either let MySQL do the filtering (and LIMIT ) for you, or you can do it yourself. Given this choice, allowing MySQL to make all working sounds much more preferable.

Having extra columns in the result set could theoretically hinder performance, but with a small result like your 20 rows, I would not expect this to have a noticeable effect.

Answer 2

It all depends on how big your tables are. If each table contains several thousand records, you can go with the first solution (UNION), and everything will be fine.

In large tables, I will probably go with the second solution, mainly because it will use much less resources (RAM) than the UNION path, and yet it will be fast enough.

But I would advise you to think about your data model and possibly optimize it. The fact that you should use UNION-based queries usually means a place to optimize, usually by merging three tables with the added "type" field (names don't work at all, but you see my point).

Answer 3

if you know your limitations, you can limit each request and have a connection only on small data. this should be better since mysql will only return 20 rows and make sorting faster than you can in php ...

 select * from ( SELECT uid, date, currency, 0, 0, 0 from table_a order by date desc limit 20 union SELECT uid, date, 0, type, 0, 0 from table_b order by date desc limit 20 ... ) order by date desc limit 20