Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 mysql table:

table t1:

  `id` bigint(20) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`)

table t2

  `id` bigint(20) NOT NULL,
  `date` int(11) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `d_id` bigint(20) DEFAULT NULL,
  `u_id` bigint(20) DEFAULT NULL,
  `browser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d_id` (`d_id`),
  KEY `u_id` (`u_id`),
  KEY `date` (`date`),
  KEY `ip` (`ip_address`)

Now I want to make such a query:

from t1,t2 
where and t1.type='article' 
group by t2.ip 
HAVING COUNT(t2.ip)>10

get all the data group by t2.ip and each t2.ip should have at least 10 results.

But the query is very slow, 300k rows data (70MB), cost 12.5290 sec, have I make bad indexes? how to optimize let query under half second? Thanks.

share|improve this question
You have a KEY type (page_type) but no page_type column. Can you correct the typo? – ypercubeᵀᴹ Mar 14 '14 at 11:43
Yes your DDL is wrong ! That is bad. – Up_One Mar 14 '14 at 12:59
I would try adding an index on (ip, d_id) – ypercubeᵀᴹ Mar 14 '14 at 17:21
@ypercube, page_type should be type some writing mistake. I have added an index on (ip, d_id) but Query took 2.5442 sec, slower than Up_One's answer. – cj333 Mar 15 '14 at 18:04
Have you tried with SELECT t2.ip FROM ... only? The other columns you have currently included in the select list will give you arbitary, indeterminate results because of the group by. – ypercubeᵀᴹ Mar 15 '14 at 21:32

I think that your type key is the main problem for you ! as you have it set to the max index size in mysql !
Try to see the real need of those 255(767 byte index) that is big for a table with that many rows. And check the distinct values in this column 'type' maybe a partition can be used in order to get rid of unnecessary reads.

share|improve this answer
thx, now i use type varchar(100) DEFAULT NULL, cost 1.5050 sec, but still feel slow, 2 tables only with 70MB... I have another innodb table, 9.3GB some query only cost 0.4245 sec. – cj333 Mar 14 '14 at 17:05
Can you run this queries and post the output ? 1-select max(LENGTH(Marks)) as length from t1; 2- select count(DISTINCT(Marks)) from t1; – Up_One Mar 14 '14 at 18:43
select max(LENGTH(type)) as length from t1, 79; select count(DISTINCT(type)) from t1, 14. Thanks. – cj333 Mar 15 '14 at 18:08
ok! now alter your table definition by making your type column to "varchar(80) not null" and create 14 partitions representing those 14 distinct type values ! make this on a diferent replicated table - for test only – Up_One Mar 17 '14 at 11:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.