optimise technique to fetching data from two large tables without using join in MySql

Question

I am working on MySQL and facing an issue in server load time. here is my table's structure and query:

I need to fetch 50 rows from message(more than 5.6 Million rows) table. The message table has attributes like (id, description, author_id, timeline_id) and the condition is, each author_id exist in users(more than 38K rows) table.

The table creation script is:

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL,
  `post_description` text() NOT NULL
  `author_id` int(11) NOT NULL,
  `timeline_id` int(11) NOT NULL,
);

ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`);

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `username` text() NOT NULL
);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

For this, I am using following SQL query:

SELECT 
  m.id
  , m.post_description
  , m.author_id
  , m.timeline_id
  , u.username 
FROM message m, users u 
WHERE m.timeline_id ='1868'
          AND m.id <= '28190' 
          AND m.author_id NOT IN (24974,7920,1498,9020,0)
          AND u.id=m.author_id 
ORDER BY m.id 
DESC LIMIT 0 ,51;

please give me suggestion to get required rows in quickest possible time.

Thanks

Please also add table creation scripts. The best way is to provide a sample in http://sqlfiddle.com/, so we can help you faster and better. — Michał Szkudlarek, Feb 22 '17 at 09:02
Joins aren't slow. Queries without proper indexes *are* slow. Add the proper indexes. You should index all the columns involved in the `where` statement — Panagiotis Kanavos, Feb 22 '17 at 10:07
BTW why do you think that putting the tables without the JOIN in `WHERE` will result in a *different* query plan? The database server is smart enough to see that the two queries are equivalent. — Panagiotis Kanavos, Feb 22 '17 at 10:08
You ask how to optimize a query (without providing the schema definitions, table statistics or an explain plan)? And insist that the solution does not use a join? Please go read some of the many questions here on query optimization. — symcbean, Feb 22 '17 at 10:11

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

It is a simple case. You need to use index on (https://dev.mysql.com/doc/refman/5.7/en/create-index.html):

messages.author_id

You do not need index on users, since primary key is by default indexed in Mysql.

Consider making use of query plan, which can help you in identifying performance issues: https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

With all above setup, your query should look like*:

SELECT 
  m.id
  , m.post_description
  , m.author_id
  , m.timeline_id
  , u.username 
FROM message m
join users u 
 on m.author_id = u.id
WHERE m.author_id NOT IN (24974,7920,1498,9020,0)
     AND m.timeline_id ='1868'
     AND m.id <= '28190' 
ORDER BY m.id 
DESC LIMIT 0 ,51;

*your original query will also be faster with above index changes, however I think that using implicit joins makes query more readable.

Edited:

As per @Panagiotis Kanavos, thanks Panagiotis, you might have to see in query plan what else causes slowness. It might occur that you need an index on m.timeline_id also.

And of course, you need to analyze table after creating an index (https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html)

Edit2:

You can also consider using a foreign key, but this is not a performance tool, it is an integrity concept: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

You can refer to that answer about FK and performance: Does introducing foreign keys to MySQL reduce performance

edited May 23 '17 at 12:31

Community

1
1

answered Feb 22 '17 at 10:03

Michał Szkudlarek

1,443
1
21
35

1

An index on `author_id` won't be enough. The server will have to scan all the user's messages. `timeline_id` should be indexed as well. – Panagiotis Kanavos Feb 22 '17 at 10:11
@MichałSzkudlarek thanks for your answer. please tell me Does foreign key concept makes any big difference. – Akshay Dixit Feb 22 '17 at 12:39
Foreign key is a data integrity concept, not a performance tool. I should have mentioned that in the answer. Will edit. – Michał Szkudlarek Feb 22 '17 at 12:53
after using 'join', no major difference in response time. is there any technique to handle big data? which can be implemented now – Akshay Dixit Feb 22 '17 at 13:28
Did you apply index and analyze tables? – Michał Szkudlarek Feb 22 '17 at 13:52
Yes, I have implemented index on author_id in 'message' table and using join but no effective difference between the previous and current server response time. – Akshay Dixit Feb 22 '17 at 14:16

optimise technique to fetching data from two large tables without using join in MySql

1 Answers1