Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 24-11-2009
Member
 
Join Date: Aug 2009
Posts: 59
LIKE query on table with three million lines

I have a small problem of performance in mysql.

I have a site of employment offer with a table of 3 million lines and the following fields:

Id, Title, Links

The column title is like:

- Business Engineer
- Engineer support business
- Case Engineer
- Engineer support case

or other
...

Obviously, the user makes a query "engineers deal" I would like the site to return with the 4 examples above. For this, I've found that with the fulltext index on the column title. Problem lies with 100000 data that I will provide a table of 3 million is impossible in terms of performance.

So I wanted your opinion on the following ideas:

1: Make a table that lists all the titles (avoiding duplication) and make a query like:
FullText on the table TITLES by then SELECT * FROM MyTable WHERE title IN (Select FULLTEXT)

What do you think? level of performance that can be acceptable?

Or two requests? The first PHP FULLTEXT then create a 2nd query like: SELECT * FROM TABLE WHERE title = title1 OR title = title2 ....

Or the judicious use LIKE with wildcards % everything going ....

In short, I let the experts give me their opinion or other tracks ...
Reply With Quote
  #2  
Old 24-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: LIKE query on table with three million lines

Developing an application with a database is not to fill a table with lots of data and then wonder how to read. For good performances you should structure information, ie start with "thinking" and then "make" and not vice versa.

For example, I guess (not knowing the "business") as "Business Engineer" will match a search type:
-Status: Framework
-Level: Engineer
-Area: Commercial
-...

I do not put people in boxes, but it's damn convenient
Reply With Quote
  #3  
Old 24-11-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: LIKE query on table with three million lines

So you indeed a WHERE MATCH ( 'title') AGAINST ( 'query')? I think it's the quickest way to such request. If it's too slow, you should limit the scope:
- Use the keyword "LIMIT"
- Forcing the user to enter a mandatory general category of job, a country and a region where the job is offered, a type of contract, ... in addition to the request.

In other words, it is already thinking ahead, when the database design, different queries that can be made and the data structure and that these requests are feasible.
Reply With Quote
  #4  
Old 24-11-2009
Member
 
Join Date: Aug 2009
Posts: 59
Re: LIKE query on table with three million lines

Thank you for these answers.

I agree with these comments but I do not see how to simplify further requests and/or tables? Because, of course, I can make a table: companies (edf, areva, areva td, ...) and a table title (engineers, developers, ....) but I do not see how it will improve the performance. I am obliged to FullText on a column title.

Similarly, if I force the user to enter a company, you must also make a fulltext column on companies to highlight all the relevant values.

example: Search Occupation: engineer, Company = Areva

must not only refer to the bids of engineer at AREVA but also an engineer at AREVA TD or engineer at AREVA tds ...

Finally, If I created several tables (title companies, ...) its what you should look my last table with links?
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "LIKE query on table with three million lines"
Thread Thread Starter Forum Replies Last Post
Update Table on Real Time, SQL query req. farhanmazhari Software Development 1 04-04-2012 11:23 PM
SQL Query For Copy records from one table to another Bellamy Software Development 4 23-05-2010 12:36 AM
SQL query to return unique output based two fields from one table Jabeen Software Development 3 16-11-2009 12:29 PM
Different ways of using sql query for adding values in the table REDBULL Software Development 3 13-11-2009 08:58 AM
How to Create view based on table-join query Preetish Windows Software 3 12-08-2009 01:44 PM


All times are GMT +5.5. The time now is 09:13 PM.