I'm on a dedicated server here, but how can I do this without crashing the server. Right now I have a cap set so that if there are 5,000+ records returned or 10 seconds has gone by, the query aborts and displays the results. I have two problems, if somene searches for something in a recent record, it would never show up because it would take longer then 10 seconds to get to that record. Second, my client wants to query against about 15 fields now, which could also mean it takes longer then 10 seconds to get any results. I have all my results broken down into very small tables of about 2000 records each and what I do is loop through a list of the tables querying each table for relevant records... which slams my MySQL server - but its the only way I could get it to work without the server crapping out on me.. Ideas ?
Have you thought about cache-ing results? I am not by any means an expert, but I think that should help your case. Other than that, Is there anyway you could make your search form be more specific, you know, like somehow divide your tables with names such that the form only goes into that table for search if that's the criteria the user is searching with, possibly by time period if your database can work with that.
You got something really wrong man.. You got 3mill rows, and taking that long? I can turn 600 million rows with full text match in under 0.850 seconds, even faster without full text match. Go back and check your indexes, if all is right then look at your queries. If you have large joins or unions go back and work on them, and dont be doing count(*) to get row count, try SQL_CALC_FOUND_ROWS. At the end of all that push in a HIGH_PRIORITY into your queries, and start caching.
Looping through tables is not the way to do it, you are better off in one table rather than lots of small ones. Full text indexes can handle up to 16 fields so having one will help you.
Well crap. Is there a better way to write my query ? SELECT t3941.EntryID, t3942.EntryID, t3943.EntryID, t3944.EntryID, t3945.EntryID, t3946.EntryID, t3947.EntryID, t3948.EntryID, t3949.EntryID, t3950.EntryID, t3951.EntryID FROM t3941, t3942, t3943, t3944, t3945, t3946, t3947, t3948, t3949, t3950, t3951 WHERE t3941.abstract LIKE '%engine%' OR t3942.abstract LIKE '%engine%' OR t3943.abstract LIKE '%engine%' OR t3944.abstract LIKE '%engine%' OR t3945.abstract LIKE '%engine%' OR t3946.abstract LIKE '%engine%' OR t3947.abstract LIKE '%engine%' OR t3948.abstract LIKE '%engine%' OR t3949.abstract LIKE '%engine%' OR t3950.abstract LIKE '%engine%' OR t3951.abstract LIKE '%engine%' LIMIT 1000 Code (markup): I have 3,174 tables... if I could query them all to get the EntryID field based on the abstract field having a particular term in a few seconds that would be great - but even running the one query above my SQL server hates it - and I'm on a standalone dedicated box.. I was looping through each table but that would mean 3,174 queries.. bad idea. The query above hits 10 tables at once which means 317 queries but still bad.. I'd like to make this more effective and I've never worked with this much data before..
You are better off having a table with 6mil rows that 3k tables with 2k rows in each, as you have found out it is a nightmare to query that many tables! Create one big table from your smaller tables, put a fulltext index on the abstract field of the big table... "ALTER TABLE `big_table` ADD FULLTEXT (abstract)" ...then run something like... SELECT id FROM big_table WHERE MATCH(abstract) AGAINST('engine') This might not be the most efficient way of doing it but way better than what you have now
Oh my, your poor server... It must be working like a dog to process that query 3 million rows isn't too much for mysql to handle... Why don't you just put everything into 1 query? The reason that query is so slow is that it needs to run full table scans because you are using LIKE. Even if you indexed the fields an index will be ignored if you use LIKE. Also, the use of OR is killing your server too, avoid OR wherever you can. You may need to read of on full text searching.
Is there a way I can get all these tables into one easily ? I know for a fact Merge is not the tool for it, because it just creates one table thats an alias for a combonation of tables - but it can only handle so much data - and it still relys on the source tables. Any ideas ?
I could help with that for a price, I manage a large unclustered MySQL database of around 250,000,000 rows.
i think you are using innodb storage engine which is very slow and do not allow fulltext search. i have a table with 1.2 million records my site is hosted on shared host which allows 20 concurrent mysql connection but my site is working fast i m using myisam storage engine so convert your storage engine to MyIsam
Been using MyISAM since wheels were square.. I figuired out how to combine the tables into one new table, I am in the process of doing that now and then will add the fulltext and try the different search method listed above.. Thanks DP