MySQLquery
When i check the number of records in a MySQL table using "show table status like 'table_name", the number of rows given is different from the number of rows when i run "select count(*)" on the same table.
I would like to know why the number is different, and what each number means.
First of all, let us look at the definition of Handler_read_rnd_next.
According to the MySQL Documentation on Handler_read_rnd_next:
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Now, look at your query:
select data from test where data = 'vwx';
You said that the table has 10 rows. As a rule of thumb, the MySQL Query Optimizer will dismiss the use of an index if the number of rows that needs to be examined is greater that 5% of the total number of rows.
Let us do the math. 5% of 10 rows is 0.5 rows. Even if the number of rows need to locate your data is 1, that is greater than 0.5. Based of this lower number of rows and that index rule I just mentioned, MySQL Query Optimizer will always do a table scan.
Since the column data is itself indexed, instead of a table scan, mysql was performed an index scan.
If you know for a certainty that the test table will never grow, you can remove all indexes and let table scans happen. Handler status variables should stop incrementing.
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;
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 found out why from the MySQL Documentation: