homepage Welcome to WebmasterWorld Guest from 54.167.138.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Searching Large Table Count with PHP & MySQL
username




msg:4305681
 12:00 am on Apr 29, 2011 (gmt 0)

Hi, I have a database which has about 200 tables in it, which need to be searched at once. Is there a best practice to search all these tables at once, other than writing an enormous UNION type query in MySQL?

PS: This is not a poor database design, just a large part of a very large whole, which cannot be separated.

Thanks in advance

 

eelixduppy




msg:4305873
 1:50 pm on Apr 29, 2011 (gmt 0)

>> This is not a poor database design

You sure? ;)

Seems like you are going to need to setup some sort of aggregate read-only view/table that combines all of these tables into a data warehouse. Not sure if MySQL would be the best dbms to use for this much data, though. or if they have tools that would make this simpler.

But once you have the DW set up, you would just query against that.

brotherhood of LAN




msg:4305891
 2:14 pm on Apr 29, 2011 (gmt 0)

If the tables are MyISAM the query should be reasonably fast anyway as there is a count in the metadata for each table. Otherwise... it'll probably be slow any other way than eelixduppy suggests.

Seb7




msg:4306118
 9:10 pm on Apr 29, 2011 (gmt 0)

This sounds like something I would create a cache table for. Dump all the bits from the other tables that you want to search, then run queries on the cache table.

eelixduppy




msg:4306312
 1:54 pm on Apr 30, 2011 (gmt 0)

Perhaps the merge storage engine could be useful? Although your tables have to be identical.

[dev.mysql.com...]

coopster




msg:4306947
 3:16 pm on May 2, 2011 (gmt 0)

Have a look at sphinx search. You can index your tables in the background with a crontab. You will only have to search the index from there forward. Lightning fast.

eelixduppy




msg:4306954
 3:26 pm on May 2, 2011 (gmt 0)

>> index

Depends on the type of queries. If a majority of the data will be returned from a query, then using an index may actually slow performance.

coopster




msg:4306984
 5:17 pm on May 2, 2011 (gmt 0)

Data is always returned with a corresponding query when using sphinx but I was referring to the context of the OP:

about 200 tables in it, which need to be searched at once.
[...]
search all these tables at once


Once the index is found (assuming this is relational data) an exact query will be substantially faster.

Hopefully that made sense now.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved