4 Replies - 268 Views - Last Post: 20 September 2018 - 04:06 AM Rate Topic: -----

#1 joseph7955   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 19-September 18

Avoiding the use of SELECT * from information_schema

Posted 19 September 2018 - 03:15 PM

hello can someone help me to add a cache for this query or for a way to optimize it by avoiding the use of SELECT * FROM information_schema to improve the performance of this query

SELECT * FROM   information_schema.key_column_usage AS kcu
       INNER JOIN information_schema.referential_constraints AS rc
               ON ( kcu.constraint_name = rc.constraint_name
                    AND kcu.constraint_schema = rc.constraint_schema )
WHERE  kcu.table_schema = 'joseph79_tiny'
       AND kcu.table_name = 'options'
       AND rc.table_name = 'options'  

SELECT * FROM       information_schema.key_column_usage        AS kcu
INNER JOIN information_schema.referential_constraints AS rc
ON         (
                      kcu.constraint_name = rc.constraint_name
           AND        kcu.constraint_schema = rc.constraint_schema ) wh ere kcu.table_schema = 'joseph79_tiny'
AND        kcu.table_name = 'links'
AND        rc.table_name = 'links'


thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Avoiding the use of SELECT * from information_schema

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2688
  • View blog
  • Posts: 10,767
  • Joined: 03-December 12

Re: Avoiding the use of SELECT * from information_schema

Posted 19 September 2018 - 03:18 PM

Why are you doing the query to begin with?
Was This Post Helpful? 0
  • +
  • -

#3 joseph7955   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 19-September 18

Re: Avoiding the use of SELECT * from information_schema

Posted 19 September 2018 - 03:56 PM

View Postastonecipher, on 19 September 2018 - 03:18 PM, said:

Why are you doing the query to begin with?


im trying to improve the performance of this query because my script is hanging on queries that appear similar to these two
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6645
  • View blog
  • Posts: 27,200
  • Joined: 12-December 12

Re: Avoiding the use of SELECT * from information_schema

Posted 20 September 2018 - 12:38 AM

Is this with SQL Server?

Describe what the queries do in plain English.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7327
  • View blog
  • Posts: 15,238
  • Joined: 16-October 07

Re: Avoiding the use of SELECT * from information_schema

Posted 20 September 2018 - 04:06 AM

One obvious cleanup would be to join table_name as well:
SELECT * 
    FROM information_schema.key_column_usage kcu
        JOIN information_schema.referential_constraints rc
            ON kcu.constraint_name = rc.constraint_name
                AND kcu.constraint_schema = rc.constraint_schema
                AND kcu.table_name = rc.table_name
        WHERE  kcu.table_schema = 'joseph79_tiny'
            AND kcu.table_name = 'options'



The SELECT * isn't really going to slow anything down at the query level, it only slows down the reading the resultset, as more data means more to read.

Hmm... this looks like MS SQL, but the information_schema.referential_constraints doesn't contain table_name in MS SQL, so MySQL?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1