0

I have two tables: data and img each raw from data having 0, 1 or n images in img table. I want to be able to get all records from data having more than 1 images in img. Cannot use JOINs because I can not edit the first part of the sql query: SELECT some_defualt_columns FROM data WHERE. Here are my solutions:

Solution 1 Takes a while to perform but works

SELECT some_defualt_columns  FROM data WHERE `id` IN
    (SELECT data_id FROM 
        (SELECT data_id, count(*) as occ FROM 
            img GROUP BY data_id 
            HAVING occ >1)
    AS tmp)

Solution 2 This should be faster than the previous (MySQL: View with Subquery in the FROM Clause Limitation) but this literally kills my MySQL server

SELECT *  FROM data WHERE id IN 
    (SELECT data_id FROM img 
         GROUP BY `data_id` 
         HAVING count(`data_id`) > 1)

SOLUTION 3 Maybe the fastes but needs the creation of a view:

CREATE VIEW my_data_with_more_than_one_img AS 
    SELECT all_columns_of_data_table FROM 
        data JOIN img 
        GROUP BY img.data_id 
        HAVING (COUNT(img.data_id) > 1

Than execute a simple SELECT on this:

SELECT * FROM my_data_with_more_than_one_img WHERE 1

This last solution is rather fast, but I want to know if is there any faster (or smarter) way to get this done

Community
  • 1
  • 1
bradypus
  • 399
  • 3
  • 18
  • How many rows do you have in each table, and how long do these queries take to run? – Tom Anderson Nov 03 '12 at 17:30
  • `img` table has about 81k records and is going to grow much bigger, `data` table has more than 300K records and this too is going to grow. Solution 1: 3,295 total, Query took 0.6065 sec (phpmyadmin data). Solution 3: 3,295 total, Query took 0.6844 sec (phpmyadmin data), but it's much faster to show up in phpmyadmin than solution 1 – bradypus Nov 03 '12 at 17:40
  • In your second solution, you appear to have used different names, and to have used `sagid` for both a table and a column. It would improve the question if you fixed that. – Tom Anderson Nov 03 '12 at 17:44
  • Sorry for the confusion of the table names: I fixed it now – bradypus Nov 03 '12 at 17:49
  • If you can't use JOINs because you can't change the outer part of the query, how were you planning to use the view? – Alain Collins Nov 03 '12 at 22:43
  • I can save views in the database; it's the php app that manages preview, pagination, forms, etc. that does not accept a custom outer part of the query. – bradypus Nov 04 '12 at 00:36

0 Answers0