Big MySQL database takes time fetching data-CodePudding

I have a big messages database there are 2.4m rows:

Showing rows 0 - 24 (2455455 total, Query took 0.0006 seconds.)

Messages, so I need conversations to load faster, for users that have less conversations it loads (user have 3.2k conversations):

 Showing rows 0 - 24 (3266 total, Query took 0.0345 seconds.) [id: 5009666... - 4375619...]

For users that have high number of conversations it loads slower (user have 40k conversations):

 Showing rows 0 - 24 (40296 total, Query took 5.1763 seconds.) [id: 5021561... - 5015545...]

I'm using index keys for these columns:

id, to_id, from_id, time, seen

Database Table:

CREATE TABLE `messages` (
  `id` int(255) NOT NULL,
  `to_id` int(20) NOT NULL,
  `from_id` int(20) NOT NULL,
  `message` longtext NOT NULL,
  `time` double NOT NULL,
  `seen` int(2) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALUES
(2, 6001, 2, 'Hi there', 1587581995.5222, 1);


ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `time_idx` (`time`),
  ADD KEY `from_idx` (`from_id`),
  ADD KEY `to_idx` (`to_id`),
  ADD KEY `seenx` (`seen`),
  ADD KEY `idx` (`id`);


ALTER TABLE `messages`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;

I'm using this query:

SELECT
  *
FROM
  messages,
  (
    SELECT
      MAX(id) as lastid
    FROM
      messages
    WHERE
      (
        messages.to_id = '1' -- ID to compare with (logged in users's ID)
        OR messages.from_id = '1' -- ID to compare with (logged in users's ID)
      )
    GROUP BY
      CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) as conversations
WHERE
  id = conversations.lastid
ORDER BY
  messages.id DESC

I don't know how to make it faster for users that have a lot of conversations, should i re create the database structure.

CodePudding user response：

Hmm, maybe you can try adding indexes to your table: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#:~:text=Creating Indexes&text=The statement to create index,the index must be distinct. Make sure to add composed Indexes by rows you are querying.

If that doesn't improve your query time, then the query should be improved.

CodePudding user response：

You can also partition the message table using the time for example.

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer. When partitioning in MySQL, it's a good idea to find a natural partition key

https://www.percona.com/blog/2017/07/27/what-is-mysql-partitioning/#:~:text=So, What is MySQL Partitioning,find a natural partition key.