How to make a faster query when joining multiple huge tables?

Question

I have 3 tables. All 3 tables have approximately 2 million rows. Everyday 10,000-100,000 new entries are entered. It takes approximately 10 seconds to finish the sql statement below. Is there a way to make this sql statement faster?

SELECT customers.name
FROM customers
INNER JOIN hotels ON hotels.cus_id = customers.cus_id
INNER JOIN bookings ON bookings.book_id = customers.book_id
WHERE customers.gender = 0 AND
customers.cus_id = 3
LIMIT 25 OFFSET 1;

Of course this statement works fine, but its slow. Is there a better way to write this code?

add indexing in both tables and try again, keep track on time or refer this [https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql] — M.Hemant, Apr 06 '19 at 08:30
I find it kinda peculiar that the "hotels" table has a cust_id. Would expect a indexed FK to customers in the bookings table. — LukStorms, Apr 06 '19 at 08:54
@LukStorms this is just a quick example I thought of. This isn't my actual table (nor is it about hotels). My actual table has proper FK's and PK's where needed. I just wanted to make the question as simple and minimal as possible. The columns are just to make the question minimal. — Ross, Apr 06 '19 at 09:30
Where's the output of `EXPLAIN`? What's the value of your `innodb_buffer_pool_size`? How much RAM do you have? What about hard drive? What's the make / model / IOPS / bandwith of the HDD? It's a clear case of I/O bound query, basic premise is to up the ram that MySQL can utilize. Use `EXPLAIN` to see what the optimizer does and whether you could index a field to help the query. — Mjh, Apr 06 '19 at 11:33

score 2 · Answer 1 · answered Apr 06 '19 at 11:23

All database servers have a form of an optimization engine that is going to determine how best to grab the data you want. With a simple query such as the select you showed, there isn't going to be any way to greatly improve performance within the SQL. As others have said sub-queries won't helps as that will get optimized into the same plan as joins.

Reduce the number of columns, add indexes, beef up the server if that's an option.

Consider caching. I'm not a mysql expert but found this article interesting and worth a skim. https://www.percona.com/blog/2011/04/04/mysql-caching-methods-and-tips/

Look at the section on summary tables and consider if that would be appropriate. Does pulling every hotel, customer, and booking need to be up-to-the-minute or would inserting this into a summary table once an hour be fine?

score 0 · Answer 2 · answered Apr 06 '19 at 09:30

0

A subquery don't help but a proper index can improve the performance so be sure you have proper index

 create  index  idx1 on customers(gender , cus_id,book_id, name )

 create index  idex2 on hotels(cus_id)

 create index  idex3 on hotels(book_id)

answered Apr 06 '19 at 09:30

ScaisEdge

131,976
10
91
107

score 0 · Answer 3 · answered Apr 06 '19 at 14:43

I find it a bit hard to believe that this is related to a real problem. As written, I would expect this to return the same customer name over and over.

I would recommend the following indexes:

customers(cus_id, gender, book_id, name)
hotels(cus_id)
bookings(book_id)

It is really weird that bookings are not to a hotel.

First, these indexes cover the query, so the data pages don't need to be accessed. The logic is to start with the where clause and use those columns first. Then add additional columns from the on and select clauses.

Only one column is used for hotels and bookings, so those indexes are trivial.

The use of OFFSET without ORDER BY is quite suspicious. The result set is in indeterminate order anyway, so there is no reason to skip the nominally "first" value.

How to make a faster query when joining multiple huge tables?

3 Answers3