4

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]

Query 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]

Query took 0.0859 sec

and if no data, MySQL returns

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

Query took 21.7332 sec

Explain 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 of the database used

CREATE TABLE IF NOT EXISTS 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 ;

--

-- Dumping data for table user_tmp

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 want the correct way to do the query and optimize database

Improve this question
5
  • 1
    +1 for complete question with all the relevant data. Commented Nov 3, 2011 at 17:24
  • If you can make some of the fields (e.g., ip and code) char or varchar types, then creating indexes on those fields (code in particular for the query shown) might help. Of course, I making a complete guess about the cardinality, though, so it may not be that helpful. Commented Nov 3, 2011 at 17:29
  • Why checkopen is text? it seems something is missing in this table structure. At first query there is check='done' and in table structure there is no "check" column but checkopen which contain numeric value (year i guess). Commented Nov 3, 2011 at 17:38
  • what does "select count(*) from user_tmp" return? Commented Nov 3, 2011 at 17:43
  • Showing rows 0 - 29 ( 4,653,050 total, Query took 0.0002 sec) Commented Nov 3, 2011 at 17:52

5 Answers 5

Reset to default
1

You don't have any indexes besides the primary key. You need to make index on fields that you use in your WHERE statement. If you need to index only 1 field or a combination of several fields depends on the other SELECTs you will be running against that table.

Keep in mind that REGEXP cannot use indexes at all, LIKE can use index only when it does not begin with wildcard (so LIKE 'a%' can use index, but LIKE '%a' cannot), bigger than / smaller than (<>) usually don't use indexes also.

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

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

Is it important to ORDER BY id? If not, leave it out, it will prevent the sort pass and your query will finish faster.

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

Comments

1

I will assume you are using mysql <= 5.1

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

I will deal with each as both are relevant for performance which is ultimately what I take your questions to be about:

  1. Column Types The difference between bigint/int or int/char for the dataip question is basically not relevant to your issue. The fundamental issue has more to do with index strategy. However when considering performance holistically, the fact that you are using MyISAM as your engine for this table leads me to ask if you really need "text" column types. If you have short (less than 255 say) character columns, then making them fixed length columns will most likely increase performance. Keep in mind that if any one column is of variable length (varchar, text, etc) then this is not worth changing any of them.

  2. Vertical Partitioning The fact to keep in mind here is that even though you are only requesting the id column from the standpoint of disk IO and memory you are getting the entire row back. Since so many of the rows are text, this could mean a massive amount of data. Any of these rows that are not used for lookups of users or are not often accessed could be moved into another table where the foreign key has a unique key placed on it keeping the relationship 1:1.

  3. Index Strategy Most likely the problem is simply indexing as is noted above. The reason that your current situation is caused by adding the "AND dataip <1319992460" condition is that it forces a full table scan.

As stated above placing all the columns in the where clause in a single, composite index will help. The order of the columns in the index will no matter so long as all of them appear in the where clause.

However, the order could matter a great deal for other queries. A quick example would be an index made of (colA, colB). A query with "where colA = 'foo'" will use this index. But a query with "where colB = 'bar'" will not because colB is not the left most column in the index definition. So, if you have other queries that use these columns in some combination it is worth minimizing the number of indexes created on the table. This is b/c every index increases the cost of a write and uses disk space. Writes are expensive b/c of necessary disk activity. Don't make them more expensive.

Improve this answer

Comments

0

You need to add index like this:

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

And if your column 'dataip' contains only unique values you can add unique key like this:

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

Keep in mind, that adding index can take long time on a big table, so don't do it on production server with out testing.

Improve this answer

4 Comments

ALTER TABLE user_tmp ADD UNIQUE(dataip); #1062 - Duplicate entry '1319506641' for key 'dataip'
ok, your values are not unique; try the first query - it will create non-unique index. Let me know if it helped
I tried did not work any result code ALTER TABLE user_tmp ADD INDEX(dataip); code The result was the worst in the query
@10neen-com, if the queries you are using always have where criteria for 'code', 'check' and 'dataip' then expand your index to include all three.
0

You need to create index on fields in the same order that that are using in where clause. Otherwise index is not be used. Index fields of your where clause.

Improve this answer

1 Comment

The order of WHERE doesn't matter - it is simple logic calculation (a AND b is the same as b AND a). Make the first part of index the field which will have better cardinality and will be possibly used alone.
0

does dataip really need to be a bigint? According to mysql The signed range is -9223372036854775808 to 9223372036854775807 ( it is a 64bit number ).

You need to choose the right column type for the job, and add the right type of index too. Else these queries will take forever.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.