Optimize SQL query in a large 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 id DES

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

Explain the plan:

counter

 SELECT count( * ) FROM user_ip_tmp 

Example used database

 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 need the right way to execute a query and optimize the database for ORDER BY id id

Answer 1

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 images, plain text will be executed).

 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 

Change If this is an option for adding and experimenting with indexes, you can try one of them (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`); 

Answer 2

You can create an index using:

  id, too and contry