Speed Up Search Of Database

[ProblemHelpPlease]

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?

[boen_robot]

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)

[ProblemHelpPlease]

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.

[boen_robot]

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)?

[ProblemHelpPlease]

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.

[justsomeguy]

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.

[boen_robot]

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.

[ProblemHelpPlease]

I will try adding fulltext indexes and see if this improves things. I may change the search options to allow the user to select per table as well, and a "searching" graphic might help pass the time!!Thanks for your help

[ProblemHelpPlease]

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.

[justsomeguy]

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;

[ProblemHelpPlease]

I am currently using truncate on the tables and then repopulating from stored csv files. I've not used the optimize command before so did'nt know what effect it would have.

[ProblemHelpPlease]

I've noticed that after performing the first search all other searches performed after are much quicker. If the data that is being searched is all server side how can it speed up client side after the first search.

[justsomeguy]

Databases use their own caching, caching isn't just on the browser.