CODEWITHSUNDEEP
mysqlsqldatabase
Ross
Ross

Reputation: 105

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

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?

Upvotes: 3

Views: 9717

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 0

KingOfAllTrades
KingOfAllTrades

Reputation: 421

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?

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

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)

Upvotes: 0

Related Questions