sql Tip 3: getting DB schema in MYSQL
long time since my last post here, well i was bit busy , hopefully i will not stop bloging for long times again.
for this blog the title actually shouldn’t be SQL tip 3 , it should be MYSQL tip 3, since 
it is talking about something specific to MYSQL, but since i had two (very old) posts about sql tips SQL tip1 SQL tip2 ,well they were geniral but were demonistrated in MYSQL too.
back to the topic. i needed to know how to get the database schema for a MYSQL DB in one of my projects and knowing the how was not stright forward so thats why i think it will be good and usefull tip.
A very usefull information in this topic is that information on database tables is stored in a system table called “information_schema.tables “.However a better information is writting the query “select * FROM information_schema.tables ” will get you some of the tables that you never had created (system tables) + your tables. to exclude system tables you have to add a where condition
“select * FROM information_schema.tables where version=’10′“ this will show only the tables you have created. version is not related to the version of your MYSQL, but older versions of MYSQL you have to write “select * FROM information_schema.tables where version=’9′“.Another simpller way you can know the table information without taking care of table version is to query based on the database name “select * FROM information_schema.tables where table_schema =’DB_Name’“
ok we have the table information but still we will need column information of these tables. These information could be extracted from another system table called information_schema.`COLUMNS`
a very simple query could give the most important information of your table’s columns is
“SELECT column_name,data_type,column_key FROM information_schema.`COLUMNS` WHERE table_schema=’DB_Name‘ and table_name = ‘your_table_name’;“
hope this very simple tip will be helpful to the readers as an extra knowledge or for those who come to this page searching through google hope this information will aid you
Read MoreSQLTip1: search database faster without like
SQL tip 2, SQL tip 3
well all of us need to deal with databases and sql query and most of the time we need to provide searching abilities to users.
well the easiest way is using like i.e to search articles containing the word “database” this query is goo enough “Select title from articles where body like ‘%database%’”.
but what if we have the articles table so huge and contains lots of rows and each row has a huge article !!!!, well simply the database work will be slowed down and will effect the work of other functions of the applications.
if this is the situation indexing the body column of the table and searching the index would be much faster (( well yeah i know this is a basic knowledge for database experts but most of the developers dont use it that why i am mentioning it))
so lets see how this is done in MYSQL:
to create the index you have to do this
ALTER TABLE articles ADD FULLTEXT(body);
then while searching the query will be something like
“select title , MATCH (body) AGAINST (‘database’) as rank from articles ORDER BY rank desc;”
this will return some thing like
|title | rank |
—————–
|sqltip | 1.3 |
| eclipse| 0 |
|.NET | 0 |
____________
where rank is 0 where it didnt found the searching keyword
and it is bigger where-ever it finds the searching keyword more (more relivent)
so to get only the good results the query will be
“select title from articles where MATCH (body) AGAINST (‘database’) > 0 ;”
Read More