How to improve this query?

Question

Dear Everyone, I have a table,

| PAGELETS | CREATE TABLE `PAGELETS` (
  `page_key` int(32) unsigned NOT NULL,
  `pagelet_serial` int(32) unsigned NOT NULL,
  `pagelet_shingle` int(32) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I would like to:

1) Find all the pagelet_shingles where quantity > 1 ( occurs more than once)
2) out of these only output those that have different page_key

This is the query that produces the a semi-correct answer:

SELECT * FROM PAGELETS WHERE pagelet_shingle IN( SELECT pagelet_shingle FROM PAGELETS GROUP BY pagelet_shingle HAVING COUNT(DISTINCT page_key) > 1) ORDER BY pagelet_shingle;

Unfortunately, on a small dataset it takes about 18 seconds;

I have another query,

SELECT dt1.* FROM 
(SELECT * FROM PAGELETS 
GROUP BY page_key, pagelet_shingle HAVING COUNT(*) = 1) 
dt1 JOIN 
(SELECT * FROM PAGELETS GROUP BY pagelet_shingle HAVING COUNT(*) > 1) 
dt2 USING (pagelet_shingle) ORDER BY pagelet_shingle

given by an expert which is not technically correct (something to do with you can't SELECT * .. GROUP ) but produces results that are A LOT faster, with the case where

SELECT * FROM PAGELETS WHERE pagelet_shingle=57

+----------+----------------+-----------------+
| page_key | pagelet_serial | pagelet_shingle |
+----------+----------------+-----------------+
|        1 |             99 |              57 | 
|        1 |             99 |              57 | 
|        2 |            228 |              57 | 
|        2 |            228 |              57 | 
+----------+----------------+-----------------+

The semi-correct query produces

+----------+----------------+-----------------+
| page_key | pagelet_serial | pagelet_shingle |
+----------+----------------+-----------------+
|        1 |             99 |              57 | 
|        1 |             99 |              57 | 
|        2 |            228 |              57 | 
|        2 |            228 |              57 | 
+----------+----------------+-----------------+

While the incorrect query doesn't have pagelet_shingle =57 in its resultset

My desired result is to have

+----------+----------------+-----------------+
| page_key | pagelet_serial | pagelet_shingle |
+----------+----------------+-----------------+
|        1 |             99 |              57 |  
|        2 |            228 |              57 | 
+----------+----------------+-----------------+

Each occuring once only.

a pagelet_shingle occuring twice in the same pagelet_serial will be omitted.

So I would like to ask the following: 1) Is there a way to to speed up the csemi orrect query to reach the speed of the incorrect one 2) or is there a way to fix the incorrect one to produce the result of the correct one ( I don't care about strictness )

Answer 1

Sounds like SELECT DISTINCT p.* ... would be your choice.

P.S. And I would really recommend the second one! make everything slow (like you just noticed) and should only be used where necessary.

Answer 2

doesn't this query solve your issue?

SELECT dt1.* FROM 
(SELECT DISTINCT * FROM PAGELETS 
GROUP BY page_key, pagelet_shingle HAVING COUNT(*) = 1) 
dt1 JOIN 
(SELECT * FROM PAGELETS GROUP BY pagelet_shingle HAVING COUNT(*) > 1) 
dt2 USING (pagelet_shingle) GROUP BY pagelet_shingle

Answer 3

What is

SELECT * FROM PAGELETS GROUP BY pagelet_serial, pagelet_shingle HAVING COUNT(*) > 0

giving you?

Answer 4

use GROUP BY and HAVING, e.g.

  SELECT *
    FROM `pagelets`
GROUP BY `pagelet_shingle`
  HAVING COUNT(*) > 1

additionally you can do a self join to output all columns, though in mysql it should work that way (different from SQL standard)

Answer 5

Judging from what I read, what you are looking for is:

SELECT DISTINCT p1.page_key, p1.pagelet_serial, p1.pagelet_shingle
  FROM PAGELETS p1
  JOIN PAGELETS p2 ON p2.page_key         = p1.page_key
                  AND p2.pagelet_serial   = p1.pagelet_serial
                  AND p2.pagelet_shingle <> p1.pagelet_shingle

That query would make full use of an index on (page_key, pagelet_serial) and should complete in tenth of seconds, not seconds.

If this was not what you were looking for, please show us what result you would expect if the values in your table were those: (1,2,3),(1,2,3),(1,1,3),(1,1,3),(1,2,4),(1,2,4),(1,1,4),(1,1,4)

Answer 6

Have you tried using exists instead of in ?

Check this out: http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/

Hope this helps