optimization of mysql select (bad performance)

Question

I have 3 tables:

t1 approx 500.000rows t2 1.600rows t3 50.000rows

My SQL query takes (around) 15sec to display results in phpmyadmin.

    SELECT * 
    FROM (
      SELECT NULL AS post_subject, t1.id_rec, t1.name, t1.id_cat, t1.date_of_record
      FROM records AS t1
       INNER JOIN categories AS t3 ON t1.id_cat = t3.id_cat AND t3.private =0
     UNION 
      SELECT post_subject, NULL , NULL , NULL , post_time
      FROM posts
     ORDER BY date_of_record DESC 
     LIMIT 10
    ) a

And indexes are:

records (t1):
 id_rec = PRIMARY
 name = INDEX
 id_cat = INDEX

categories (t3):
 id_cat = PRIMARY

posts:
 post_subject = INDEX

The goal is to get 10rows which are ordered based on DATE_OF_RECORD && POST_TIME. Table of records and posts are not connected and they represent different data. I am trying to get some kind of "log" from my data... (t1&&t3),(posts).... as it goes in time...

Could you help me please with optimization?

For future reference, when you've accepted an answer the question will be styled differently wherever it appears on the site; there's no need to edit "solved" into the title. :) — Dan J
– Dan J, Commented Nov 15, 2013 at 16:14

Filipe Silva · Accepted Answer · 2013-11-15 16:06:15Z

2

You could try adding LIMIT 10 to the first part of the UNION too. And if you really only want 10, you would LIMIT 10 to the final result: To get the 10 latest of both groups you should only put the ORDER BY and LIMIT outside, but if you can live with that, it may be faster:

SELECT *
FROM ((
  SELECT NULL AS post_subject, t1.id_rec, t1.NAME, t1.id_cat, t1.date_of_record
  FROM records AS t1
  INNER JOIN categories AS t3
    ON t1.id_cat = t3.id_cat
      AND t3.private = 0
  ORDER BY date_of_record DESC LIMIT 10)      
  UNION      
  (SELECT post_subject, NULL, NULL, NULL, post_time
  FROM posts
  ORDER BY post_time DESC LIMIT 10)
  ) a
ORDER BY date_of_record DESC LIMIT 10

Additionally, make sure you have indexes on posts.post_time and records.date_of_record to make this execute faster.

edited Nov 15, 2013 at 16:06

answered Nov 15, 2013 at 15:51

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vatev Over a year ago

The 2-nd ORDER BY has to be post_time DESC. And he will need to have indexes on records.date_of_record and posts.post_time.

Kamil B. Over a year ago

This gives me: #1221 - Incorrect usage of UNION and ORDER BY :(

Vatev Over a year ago

@FredericoRossini throw some ()-s at it :)

Kamil B. Over a year ago

(MySQL: 5.5.31, phpMyAdmin - 2.9.0.2)

Filipe Silva Over a year ago

@FredericoRossini. Try now with extra ()'s .

|

Collectives™ on Stack Overflow

optimization of mysql select (bad performance)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related