Your last query
mysql> select table_name from tables as a left join (select distinct table_name from tables as b) on a.table_name!=b.table_name;
ERROR 1248 (42000): Every derived table must have its own alias
fails because the as b
must be outside the parentheses like this
mysql> select table_name from tables as a left join (select distinct table_name from tables) as b on a.table_name!=b.table_name;
Your method 2 failed
mysql> select count(table_name) as num, table_name from tables group by table_name where num > 1;
because the group by table_name
must be last like this
mysql> select count(table_name) as num, table_name from tables where num > 1 group by table_name;
SUGGESTION
Perhaps you should start with a GROUP BY ... HAVING
query like this
SELECT table_name,COUNT(1) table_count
FROM information_schema.tables
WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
GROUP BY table_name HAVING COUNT(1) > 1;
This will definitely give all tables whose name appears in multiple databases
Form that as a subquery and join it to gather all databases the table appears in
SELECT
A.table_name,
GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
FROM
(
SELECT table_name,COUNT(1) table_count
FROM information_schema.tables
WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
GROUP BY table_name HAVING COUNT(1) > 1
) A INNER JOIN information_schema.tables B
USING (table_name) GROUP BY A.table_name;
Please notice that I use INNER JOIN
rather than LEFT JOIN
because it will really form a Cartesian product (2704 X 2704) and then perform comparisons.
I know this works because I tried it out in MySQL 5.5.12 on my Windows7 machine
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.12-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT
-> A.table_name,
-> GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
-> FROM
-> (
-> SELECT table_name,COUNT(1) table_count
-> FROM information_schema.tables
-> WHERE table_schema NOT IN
-> ('information_schema','performance_schema','mysql')
-> GROUP BY table_name HAVING COUNT(1) > 1
-> ) A INNER JOIN information_schema.tables B
-> USING (table_name) GROUP BY A.table_name;
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| table_name | TheTableAppearsInTheseDatabases |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| a | junk,test,robottinosino |
| acl | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive |
| blocks | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| blog | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive |
| blog_category | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| blog_entrycat | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive |
| blog_meta | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_archive,weisci_jaws_staging2 |
| blog_trackback | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging |
| calendar_events | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2 |
| calendar_meta | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging |
| calendar_questions | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2 |
| calendar_tickets | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging |
| calendar_transactions | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive |
| captcha_complex | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2 |
| change_log | test,junk |
| chat_staff | test,junk |
| comments | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive |
| donations_charities | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2 |
| donations_charities_meta | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| donations_donations | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| foo_reference1 | timpost1,timpost2 |
| foo_reference2 | timpost1,timpost2 |
| foo_reference3 | timpost1,timpost2 |
| groups | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2 |
| ipvisitor | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| job_post | giannosfor,test |
| layout | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| listeners | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| mediamanager_files | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging |
| mediamanager_group | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| mediamanager_photos | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| mediamanager_video | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| menus | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| menus_groups | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| mytable | ryanzec,user1267617,cabita,dotancohen,johnlocke,neeraj,test,user391986,cool_cs,javier,mathieu |
| mytext | jakobud,newstuff |
| occupation_field | giannosfor,test |
| policy_agentblock | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| policy_ipblock | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| prova | veto,vito |
| registry | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| registry_bk | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| session | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| static_pages | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| static_pages_translation | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| t | preeti,rollup_test |
| t1 | abidibo,test |
| t2 | test,abidibo |
| t3 | test,abidibo |
| table1 | table_test,supercoolville,test |
| table2 | supercoolville,test,table_test |
| tags | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| tags_content | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| tbl_banner_position | weisci_jaws_staging2,weisci_jaws_live |
| tbl_banner_upload | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| tbl_global_banner | weisci_jaws_staging2,weisci_jaws_live |
| tms_authors | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| tms_repositories | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| tms_themes | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging |
| updates | test,junk |
| url_aliases | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive |
| url_maps | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| users | weisci_jaws_archive,weisci_jaws_staging,giannosfor,veto,weisci_jaws_live,weisci_jaws_staging2,friends,sample,vito |
| users_groups | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2 |
| users_meta | weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
65 rows in set (0.91 sec)
mysql>
Give it a Try !!!
Best Answer
I mocked up data by looking at the row counts in your tables, giving them an even data distribution, and making guesses about the schema:
The most important assumption that I made about the schema is that the
Id
column is the primary key of the[Conference]
table. This seemed reasonable given the query plan and the index names involved.On my machine I get the same query plan as you, but my starting query only takes 163 ms of CPU. I assume the differences come down to differences in hardware, data distribution, and the fact that I'm not returning data to the client.
The first thing that jumped out to me is the unnecessary
GROUP BY
in yourconfData
derived table.Id
is the primary key of the table so you don't need all of the aggregates. With the right indexes (which you already have for this particular case), subqueries aren't necessarily a bad thing. Rewriting what you have to remove theGROUP BY
:This results in the stream aggregate getting pushed down further into the plan:
The uploaded plan takes 113 ms of CPU. The same operators are present, but some of them process fewer rows which saves time. You may be able to make this query more efficient by defining a covering index on the
[Conference]
withId
as the index key. This may seem like an odd thing to do, but your clustered index scan takes 10% of the overall query time and likely includes columns that you don't need.If you want to make the query faster you could also consider an indexed view. Why perform the aggregation every time when you can define a simple indexed view to do it for you?
This will result in a little more space and a little bit of overhead when doing DML on the table. Overall I'd say that it's a good use case for an indexed view. Rewriting the query again:
SQL Server agrees with my assessment that it's a good idea and CPU time falls to 78 ms.
On my machine I was able to make the query even faster, but this is starting to get into optimizations that are somewhat risky because it may require a
LOOP JOIN
hint. That hint may not be a good idea as your query or the data in the table changes. It also may not be a good fit for your hardware. The idea behind this approach is to create a suitable index on[Conference]
and to take full advantage of theTOP
with a plan that only does nested loops. Here is the index that I added:Running the same query as before with a
LOOP JOIN
hint gave me the following plan:That query only took 58 ms of CPU time. It's worth mentioning that I noticed requesting the actual plan adds quite a bit of relative overhead at this stage. All of the other possible optimizations that come to mind to me aren't safe for production so I'll stop here.
As a final thought, do you really want to return 1000 arbitrary rows and the minimum conference name? Is that information useful to your end users?