Jump to content

Speed Up Search Of Database


ProblemHelpPlease
 Share

Recommended Posts

I need to speed up the search of the database I am using. It currently takes upto 20 seconds to return results. The database is made up of 27 tables totalling 350,000+ rows and around 450mb. A part of the code I am using is below. I ideally could also do with selecting everything rather than the first 3 columns but the total of all columns is over 30.

SELECT SET1, SET2, SET3 FROM table1 WHERE NAME LIKE '%$terms%' UNION ALL

Any ideas?

Link to comment
Share on other sites

Do you really need "UNION ALL"? Or is there more to this query that you're not showing?If you aren't going to process all results (i.e. not all of them are going to be displayed or whatever), consider adding a LIMIT clause at the end. That way, searching will stop after that amount is returned, like:

SELECT SET1, SET2, SET3 FROM table1 WHERE NAME LIKE '%$terms%' LIMIT 30

(assuming you wanted the first 30 records)

Edited by boen_robot
Link to comment
Share on other sites

Sorry I should of eplained a bit more of the code. The query is built from a For loop that adds union all to the end of each statement until all 27 table are part of the query then it limits the record selection to 50. Each page then has the standard previos and back buttons for the next 50 or previous 50 records.The code uses data from other files so I could only write a small part of the code here. I have other large databases on other sites that take a very short time to search but this database does have a lot more columns than any other database.

Link to comment
Share on other sites

Is it possible that instead of unions, you could use the direct table.column syntax, like:

SELECT table1.SET1, table1.SET2, table1.SET3, table2.SET1, table2.SET2 FROM table1 INNER JOIN table2 USING(SET1) WHERE NAME LIKE '%$terms%'

(+another INNER JOIN for each table, etc.) or something similar... I don't know... something which connects the tables. Do you really have to fetch them all? Why not fetch only the stuff matching certain criterias (as with the JOIN's)?

Edited by boen_robot
Link to comment
Share on other sites

Their are no records that have matching details, this is why I wasn't using inner join. The data in the tables is updated from an external source daily making adding extra fields to the database difficult. It also means I can't always be sure of the exact content of the database only the structure. Would the LIKE be causing a delay, I wondered if this could be improved.

Link to comment
Share on other sites

I think the whole "union 27 tables of over 30 columns each" thing is causing a delay. Anything you do causes a delay though. Using a WHERE clause causes a delay. If you want to speed up the LIKE, make sure that all of the columns you're searching on in every table have indexes on them. It looks like you need fulltext indexes on those columns, if you don't already have them.

Link to comment
Share on other sites

Depends on what you want to match with LIKE.

%a%

matches any element that contains the letter "a". So does

a

But the former also tries to find zero or more characters (any) at that position. Having said that, unless $terms may contain "%" as well (which would therefore create a whole new meaning of the pattern), there's no point in using it around the LIKE clause.

Edited by boen_robot
Link to comment
Share on other sites

I have just tried running an optimize tables command and the search time was cut from 20 seconds to about 5. I drop the tables and then repopulate them each day. Would this be effecting the delay. Do you think running an optimize tables command on a cron job would be beneficial and if so how often.

Link to comment
Share on other sites

If you're dumping the table and repopulating it you should optimize it then. If you're using a DELETE query to remove everything, it will probably be more efficient (and faster to delete) if you used TRUNCATE TABLE instead. I don't think you need to optimize if you truncate it. I run these commands daily to copy one table to another:TRUNCATE TABLE content_session_archive;TRUNCATE TABLE content_session_history_archive;INSERT INTO content_session_archive SELECT * FROM content_session;INSERT INTO content_session_history_archive SELECT * FROM content_session_history;

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...