-1

I have a table where I log members.

There are 1,486,044 records here.

SELECT * FROM `user_log` WHERE user = '1554143' order by id desc

However, this query takes 5 seconds. What do you recommend ?

Table construction below;

CREATE TABLE IF NOT EXISTS `user_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `operation_detail` varchar(100) NOT NULL,
  `ip_adress` varchar(50) NOT NULL,
  `l_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
COMMIT;
GMB
  • 216,147
  • 25
  • 84
  • 135
perfectmorn
  • 7
  • 2

2 Answers2

2

For this query:

SELECT * FROM `user_log` WHERE user = 1554143 order by id desc

You want an index on (user, id desc).

Note that I removed the single quotes around the filtering value for user, since this column is a number. This does not necessarily speeds things up, but is cleaner.

Also: select * is not a good practice, and not good for performance. You should enumerate the columns you want in the resultset (if you don't need them all, do not select them all). If you want all columns, since your table has not a lot of columns, you might want to try a covering index on all 5 columns, like: (user, id desc, operation_detail, ip_adress, l_date).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Sorry but not an adequate answer. I want the query to be fast. The example you have given has nothing to do with this. – perfectmorn Aug 25 '20 at 01:04
  • 1
    @perfectmorn: I recommended one index (well, actually, two indexes) for your query so it runs fast. What seems inadequate to you? – GMB Aug 25 '20 at 01:05
  • Can you explain this with the full code example. It's the same if I need to make changes to the table. – perfectmorn Aug 25 '20 at 01:06
  • @perfectmorn: just use [`create index`](https://dev.mysql.com/doc/refman/8.0/en/create-index.html), as in: `create index myidx on user_log(user, id desc)`. – GMB Aug 25 '20 at 01:09
  • Good. I just indexed the user column and there was a great performance increase. What exactly does it mean to define an index? Is there any problem with the entries? Because more than one record can be entered for a user. – perfectmorn Aug 25 '20 at 01:17
  • Can you answer please – perfectmorn Aug 25 '20 at 01:26
  • See: https://stackoverflow.com/questions/1108/how-does-database-indexing-work – catcon Aug 25 '20 at 01:33
  • @perfectmorn 'not an adequate answer' ? Seriously? I'll remember to ignore your questions in future. – Strawberry Aug 25 '20 at 06:37
0

In addition to the option of creating an index on (user, id), which has already been mentioned, a likely better option is to convert the table to InnoDB as create an index only on (user).

Gordan Bobić
  • 1,748
  • 13
  • 16