Question 386679 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is there any other method to stop join when first matching column is found?

I’m using MySQL database. I have to join two tables (A, B). They have relationship (N->N). Actually, A is chatting room table and B is chat (chat record).

Chatting room has many chats. If a user enters chat app, the list of chat rooms should be orderd by the latest received chat.

But at this moment, chatting room has too many chats, so join performance is severely degraded. So, my idea is :

  1. Chat table is ordered by its created time. So, this is a type of time series data.

  2. Chatting room finds chat table reversely, therefore they can find the latest created chat.

  3. When certain chatting room finds its first chat, IT STOPS SEARCH FOR JOIN.

My question is :

  1. Is there any other method to find columns reversely?

  2. Is there any other method to stop on the moment that a certain column is matched(found) when two tables join?

Chat

CREATE TABLE `chat` (
  `chat_room_id` bigint DEFAULT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `member_id` bigint DEFAULT NULL,
  `subject_id` bigint DEFAULT NULL,
  `content` varchar(255) NOT NULL,
  `message_type` enum('POST','IMAGE','TEXT') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK44b6elhh512d2722l09i6qdku` (`chat_room_id`),
  KEY `FKgvc5hrt0h18xk63qosss3ti30` (`member_id`),
  CONSTRAINT `FK44b6elhh512d2722l09i6qdku` FOREIGN KEY (`chat_room_id`) REFERENCES `chat_room` (`id`),
  CONSTRAINT `FKgvc5hrt0h18xk63qosss3ti30` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
)

Chat Room

CREATE TABLE `chat_room` (
  `member_cnt` int DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `room_type` enum('GROUP','PERSONAL') DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Thank you.

Tags:

2

Answers


  1. 0

    You can do something like

    select chat_room.*
from chat
join chat_room
on chat.chat_room_id = chat_room.id
left join chat newer_chat
on newest_chat.chat_room_id = chat_room.id and newer_chat.created_at > chat.created_at
where newer_chat.id is null
order by chat.created_at desc

    this joins the chat rooms with individual chats, provided that there is no newer chat than the one we join with. Now, if this is not quick-enough, then you might want to make sure you have an index on (chat_room_id, created_at) for the chat table, which will greatly increase the speed of this search. Alternatively you could have a last_chat_in_room table where, whenever you insert a new chat, its corresponding record in this table would be properly upserted so it will be up-to-date and use this aggregate table rather than the chat table. Or you could have a cron job ranking the rooms by latest chats and caching the result and reading from that cache.

    Login or Signup to reply.
  2. 0

    Study EXISTS — it is called a "semi-join", meaning that it stops when a matching row is found.

    It is usually seen in

    WHERE EXISTS ( SELECT 1 FROM t2 WHERE ... )

    In that situation it is stops looking into t2 as soon as it’s WHERE clause succeeds.

    LEFT JOIN t2 ON ...
WHERE t2.id IS NULL

    is similar to

    WHERE NOT EXISTS ( SELECT 1 FROM t2 WHERE ... )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search