How to optimize the database for this query in a large database?

Question

Query

SELECT id FROM `user_tmp` WHERE `code` = '9s5xs1sy' AND `go` NOT REGEXP 'http://www.xxxx.example.com/aflam/|http://xx.example.com|http://www.xxxxx..example.com/aflam/|http://www.xxxxxx.example.com/v/|http://www.xxxxxx.example.com/vb/' AND check='done' AND `dataip` <1319992460 ORDER BY id DESC LIMIT 50 

MySQL returns:

 Showing rows 0 - 29 ( 50 total, Query took 21.3102 sec) [id: 2622270 - 2602288] 

Request took 21.3102 sec.

if i remove

And dataip <1319992460

MySQL returns

 Showing rows 0 - 29 ( 50 total, Query took 0.0859 sec) [id: 3637556 - 3627005] 

Request took 0.0859 seconds

and if there is no data, MySQL returns

 MySQL returned an empty result set (ie zero rows). ( Query took 21.7332 sec ) 

Request took 21.7332 seconds

Explain the plan:

  SQL query: Explain SELECT * FROM `user_tmp` WHERE `code` = '93mhco3s5y' AND `too` NOT REGEXP 'http://www.10neen.com/aflam/|http://3ltool.com|http://www.10neen.com/aflam/|http://www.10neen.com/v/|http://www.m1-w3d.com/vb/' and checkopen='2010' and `dataip` <1319992460 ORDER BY id DESC LIMIT 50; Rows: 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE user_tmp index NULL PRIMARY 4 NULL 50 Using where 

Example used database

CREATE A TABLE IF user_tmp DOESN’T EXIST user_tmp ( id int (9) NOT NULL AUTO_INCREMENT, ip text NOT NULL, dataip bigint (20) NOT NULL, ref text NOT NULL, click int (20) NOT NULL, code text NOT NULL, too text NOT NULL, name text NOT NULL, checkopen text 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`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4653425 ; 

-

- reset data for user_tmp table

 INSERT INTO `user_tmp` (`id`, `ip`, `dataip`, `ref`, `click`, `code`, `too`, `name`, `checkopen`, `contry`, `vOperation`, `vBrowser`, `iconOperation`, `iconBrowser`) VALUES (1, '54.125.78.84', 1319506641, 'http://xxxx.example.com/vb/showthread.php%D8%AA%D8%AD%D9%85%D9%8A%D9%84-%D8%A7%D8%BA%D9%86%D9%8A%D8%A9-%D8%A7%D9%84%D8%A8%D9%88%D9%85-giovanni-marradi-lovers-rendezvous-3cd-1999-a-155712.html', 0, '4mxxxxx5', 'http://www.xxx.example.com/aflam/', 'xxxxe', '2010', 'US', 'Linux', 'Chrome 12.0.742 ', 'linux.png', 'chrome.png'); 

I need the right way to execute a query and optimize the database

Answer 1

You have no indexes other than the primary key. You need to index the fields that you use in the WHERE clause. If you need to index only one field or a combination of several fields, it depends on the other SELECTs that you will perform against this table.

Keep in mind that REGEXP cannot use indexes at all, LIKE can use an index only when it does not start with a template (therefore LIKE 'a%' can use an index, but LIKE '%a' cannot), more / less than (<>), as a rule, also do not use indexes.

So, you are left with the code and check fields. I believe that many rows will have the same value for check , so I would start the index with the code field. Multi-field indexes can only be used in the order in which they are defined ...

Imagine the index created for the code, check fields. This index can be used in your query (where the WHERE clause contains both fields), also in the query with the code field only, but not in the query with the check field only.

Is this important for an ORDER BY id ? If not, leave it there, this will prevent the sort from going through, and your request will complete faster.

Answer 2

I assume that you are using mysql <= 5.1

The answers above fall into two main categories: 1. You are using the wrong type of column 2. You need indexes

I will deal with each, since both of them are related to performance, which ultimately relates to the following issues:

• Column Types The difference between bigint / int or int / char for the data transfer issue is mostly irrelevant to your problem. The main problem is rather related to the index strategy. However, when you consider performance holistically, the fact that you are using MyISAM as your engine for this table makes me ask if you really need text column types. If you have short (less than 255 words) character columns, then creating fixed-length columns is likely to increase performance. Keep in mind that if any one column has a variable length (varchar, text, etc.), then it is not worth changing any of them.

• Vertical Partition Keep in mind that even if you only query the id column in terms of disk I / O and memory, you get the whole row back. Since many lines are text, this can mean a huge amount of data. Any of these lines, which are not used to search for users or are not often available, can be transferred to another table, where the foreign key has a unique key placed on it, while maintaining a 1: 1 ratio.

• Index Strategy Most likely, the problem is simple indexing, as indicated above. The reason that your current situation is caused by the addition of the condition "AND dataip <1319992460" is because it forces a full table scan.

As stated above, placing all the columns in the where clause in one composite index will help. The order of the columns in the index will matter until they all appear in the where clause.

However, ordering can go a long way for other queries. A quick example is an index made from (colA, colB). A query with "where colA = 'foo" will use this index. But the query with "where colB =" bar "will not, because colB is not the largest column in the index definition. Thus, if you have other queries that use these columns in some combination, it is worth minimizing the number of indexes created in This is b / c, each index increases the cost of recording and uses disk space. It records expensive b / c of required disk activity. Do not make them more expensive.

Answer 3

You need to add the index as follows:

 ALTER TABLE `user_tmp` ADD INDEX(`dataip`); 

And if your "dataip" column contains only unique values, you can add a unique key as follows:

 ALTER TABLE `user_tmp` ADD UNIQUE(`dataip`); 

Keep in mind that adding an index can take a long time on a large table, so do not do this on a production server without testing.

Answer 4

You need to create the index in the fields in the same order as in the where clause. Otherwise, the index will not be used. The index fields of your where clause.

Answer 5

should dataip really be bigint? According to mysql Signed range: -9223372036854775808 - 9223372036854775807 (this is a 64-bit number).

You need to select the correct column type for the job and add the desired index type. Otherwise, these requests will be forever.