Alpha
Share and Organize Code
login register
HotNewTop
F.A.Q.
1. To begin adding code first register.
2. Create your own tags for code that you add.
3. To see your own code click Me, to see all code click Everyone, or filter by people rob.
4. To follow a user, click on their username, rob and hit follow.
5. Add other code to your library by clicking the
button near the code titles (note that you can not edit these codes).
6. To create a copy of a code that you can edit use fork.
7. If you can't find your code, double check your filter settings as you can filter by Category, User and Tag simultaneously.
8. Add comments to a code by clicking on it's title or the comments link underneath it.

Find Worst Indexes in all Databases (plain)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*
SQL script to grab the worst performing indexes in all databases
*/
SELECT
  t.TABLE_SCHEMA AS `db`
, t.TABLE_NAME AS `table`
, s.INDEX_NAME AS `index name`
, s.COLUMN_NAME AS `field name`
, s.SEQ_IN_INDEX `seq in index`
, s2.max_columns AS `# cols`
, s.CARDINALITY AS `card`
, t.TABLE_ROWS AS `est rows`
, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
FROM INFORMATION_SCHEMA.STATISTICS s
INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
INNER JOIN (
  SELECT
     TABLE_SCHEMA
   , TABLE_NAME
   , INDEX_NAME
   , MAX(SEQ_IN_INDEX) AS max_columns
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA != 'mysql'
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS s2
ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
AND s.TABLE_NAME = s2.TABLE_NAME
AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
LIMIT 20;
/*
Change this row limit to whatever you prefer
...
*/
[+] (36) more...
comments (1) flag fork
This query will show the 'top 20' bad indexes assuming you have more than 20 bad indexes. Change the LIMIT number at the bottom of the query. - prdgreene 5 Years ago