PHP/MYSQL Database Tuning/Optimizing

Hey there all ssup! my xams are over now .. though there had been quite couple of things going around with my lyfe i finally took out some spare time to write a blog entry.. Well quite recently i have encountered a problem which is quite weird i have setup a small site with a huge data set.. really huge… My DB Structure was fairly simple 3 tables were there for illustrative purpose i will name them

Table_A
Table_B
Table_A_B (Relation ship of A and B )

Table A contained Lots and lots of data where as table B have relatively small number of data in it.

In my interface application there was an option of Search in which i was fetching out data from Table_A with relationship of Table_A_B

the query i used to fetch out rows was like

SELECT col1,col2
FROM Table_A WHERE id_TableA IN (

SELECT TableA_id
FROM Table_A_B
WHERE Table_Bid =1
)
AND Col1 Like (‘%query%’);

With a data set of 300000 it took 2 minutes to retrieve the search result out of it. That was really pissing me off.

After executing the same query with Explain i found out only one index was being hit which was one of the reason of query taking so long Now i have to make indexes.

Then i Google around and found that i could make Full-text search index on the col1 But guess what Mysql Only supports Full Text search indexes on MyISAM Tables and i had InnoDB Tables which cannot be transformed into MyISAM due to constraints.. Shit Happens.. Nevertheless so Now in this case what i did was to replicate the tables and set my default table type to MyISAM this was easy

Create Table Table_A_Copy SELECT * From Table_A

After doing that you will have a copy of the Table_A however no indexes / Primary key/ FK Constraints  would be there you have to make a primary key plus indexes and now this time you can make Full Text Indexes.

And off course you have to change your code/Query  to use the new tables there would be a marginal increase in searching after doing this ;) the query would look some thing like this
SELECT col1,col2
FROM Table_A WHERE id_TableA IN (

SELECT TableA_id
FROM Table_A_B
WHERE Table_Bid =1
)
AND Match(col1) AGAINST(‘”search query”‘ IN BOOLEAN MODE)

Mysql Reference manual for Full text search can be found out here

see ya folks and a very happy new year to all of ya :)

~ by Azeem on December 26, 2009.

4 Responses to “PHP/MYSQL Database Tuning/Optimizing”

  1. [...] This cup of tea was served by: Azeem Personal play ground [...]

  2. drop a line if interested in career opportunities. thnks.

  3. Hi,

    Why don’t you write anymore?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: