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
Posted in APACHE MYSQL PHP, Code, Codes, LAMP, Linux, mysql, mysqldump, php
Tags: optimization, php, searching php table, Table Search
Recent Comments