Optimize SQL-query in big database?

Question

Query

SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
ORDER BY id DESC
LIMIT 50

MySQL returns:

Showing rows 0 - 29 ( 50 total, Query took 11.9276 sec) [id: 3452538 - 3448824]

if i remove ORDER BY id DESC

Showing rows 0 - 29 ( 50 total, Query took 0.0033 sec)

Explain plan:

count

SELECT count( * )
FROM user_ip_tmp

enter image description here

Example of the database used

CREATE TABLE IF NOT EXISTS `user_ip_tmp` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `ip` varchar(20) NOT NULL,
  `dataip` bigint(20) NOT NULL,
  `ref` text NOT NULL,
  `click` int(20) NOT NULL,
  `code` varchar(17) NOT NULL,
  `too` text NOT NULL,
  `checkopen` varchar(17) NOT NULL,
  `contry` text NOT NULL,
  `vOperation` text NOT NULL,
  `vBrowser` text NOT NULL,
  `iconOperation` text NOT NULL,
  `iconBrowser` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ip` (`dataip`),
  KEY `ip` (`checkopen`),
  KEY `ip` (`code`),
  KEY `ip` (`too`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5426268 ;

I want the correct way to do the query and optimize database for ORDER BY id DESC

Kenster · Accepted Answer · 2016-10-07 14:42:00Z

2

It would be interesting to know something about the distribution of your data. Could you add the results of the following queries to your post? (no need for pictures, plain text will do).

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/' AND contry != 'CN' AND contry != 'TW'; 

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/';

Also, could you test this alternative for performance? EDIT: alias for subquery

SELECT sub.* FROM
(SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
) sub
ORDER BY sub.id DESC
LIMIT 50

Edit If it is an option to add and experiment with indexes, then you could try one of these (or both and see which is better)

CREATE INDEX index_name ON `user_ip_tmp` (`too`, `id`);
CREATE INDEX index_name ON `user_ip_tmp` (`too`, `contry`, `id`);

edited Oct 7, 2016 at 14:42

Kenster

25.8k23 gold badges87 silver badges116 bronze badges

answered Nov 14, 2011 at 11:16

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

10neen com Over a year ago

SELECT count() FROM user_ip_tmp WHERE too = 'xxx.com' AND contry != 'CN' AND contry != 'TW'; 42171 SELECT count() FROM user_ip_tmp WHERE too = 'xxx.com'; 42879 ` SELECT * FROM ( SELECT * FROM user_ip_tmp WHERE too = 'xxx.com' AND contry != 'CN' AND contry != 'TW' ) ORDER BY id DESC LIMIT 50 no work Shows the problem #1248 - Every derived table must have its own alias

bpgergo Over a year ago

yupp, I forgot to give an alias to the subquery I used in the from clause. I edited my answer and added it, thanks

10neen com Over a year ago

Server stops working after it CREATE INDEX index_name ON user_ip_tmp (too, id); I think because there are more than 6 million entries and area of base 1 GB

DRapp Over a year ago

@10neencom, having a "text" field with no defined "length" is probably what is killing your server during create index. I would index on say... left 200 or so characters of the "too" URL, then the ID.

Shawn Chin · Accepted Answer · 2011-11-14 12:16:54Z

0

You can create an index with:

 id, too and contry

edited Nov 14, 2011 at 12:16

Shawn Chin

87.4k20 gold badges168 silver badges193 bronze badges

answered Nov 14, 2011 at 12:11

altmannmarcelo

1012 bronze badges

1 Comment

10neen com Over a year ago

I do not think will solve the problem I think the problem in anything else I do not know why when make ORDER BY id DESC Cause a rise in the query

Collectives™ on Stack Overflow

Optimize SQL-query in big database?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related