From: Peter Brawley Date: May 12 2005 7:00am Subject: Re: table optimisation List-Archive: http://lists.mysql.com/mysql/184004 Message-Id: <4282FF14.1050603@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-4282FF15056E=======" --=======AVGMAIL-4282FF15056E======= Content-Type: multipart/alternative; boundary=------------080708050900040609010306 --------------080708050900040609010306 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Seena, >How to check whae are tables are having how many >indexes on which columns ? http://dev.mysql.com/doc/mysql/en/show-index.html and alternatively if you're using 5.03 or later, http://dev.mysql.com/doc/mysql/en/statistics-table.html eg an approximate equivalent of SHOW KEYS FROM tbl [FROM db] would be SELECT * FROM information_schema.statistics WHERE table_name='tbl' [AND table_schema = 'db'] PB ----- Seena Blace wrote: >How to check whae are tables are having how many indexes on which columns ? > >Thomas Sundberg wrote: > > >>-----Original Message----- >>From: Seena Blace [mailto:sqlserverdb@stripped] >>Sent: den 11 maj 2005 23:58 >>To: mysql@stripped >>Subject: table optimisation >> >>Hi, >>I have been noticing table performanace issue when # of rows >>grows more.How to tune that table? >> >> > >Adding index on the column or columns that are often used in queries could >be a start. Performing full table scan on large, unindexed tables can take >quite longtime when the number of rows are increasing. > >/Thomas > > > > >--------------------------------- >Yahoo! Mail > Stay connected, organized, and protected. Take the tour > > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 > > --------------080708050900040609010306 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Seena,

>How to check whae are tables are having how many
>indexes on which columns ?

http://dev.mysql.com/doc/mysql/en/show-index.html

and alternatively if you're using 5.03 or later, http://dev.mysql.com/doc/mysql/en/statistics-table.html

eg an approximate equivalent of SHOW KEYS FROM tbl [FROM db] would be
  SELECT *
  FROM information_schema.statistics
  WHERE table_name='tbl'
  [AND table_schema = 'db']

PB

-----

Seena Blace wrote: 
How to check whae are tables are having how many indexes on which columns ?

Thomas Sundberg <thomas.sundberg@stripped> wrote:
  
-----Original Message-----
From: Seena Blace [mailto:sqlserverdb@stripped] 
Sent: den 11 maj 2005 23:58
To: mysql@stripped
Subject: table optimisation

Hi,
I have been noticing table performanace issue when # of rows 
grows more.How to tune that table?
    

Adding index on the column or columns that are often used in queries could
be a start. Performing full table scan on large, unindexed tables can take
quite longtime when the number of rows are increasing.

/Thomas



		
---------------------------------
Yahoo! Mail
 Stay connected, organized, and protected. Take the tour
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005
--------------080708050900040609010306-- --=======AVGMAIL-4282FF15056E======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 --=======AVGMAIL-4282FF15056E=======--