Simple Select Query taking too much time in mysql-table with large data-set 10M

Question

SELECT *
FROM many_leads_lead_details
WHERE location LIKE '%Los%Angeles%'
  AND (keywords LIKE '%Real%' or
       keywords LIKE '%Real Estate%' or
       keywords LIKE '%Real Estate Agent%')

above query is taking too much time as compared to localhost database.

the database taking time is hosted on another server, response time as below Localhost = 30.00 Seconds and on rds.amazonaws.com = 1.50 Minuts

$leads2 = LeadDetails::query();
        $temp = '';
        $location = str_replace(' ', '%', explode(',', $campaign->location)[0]);
        $leads2->Where('location', 'like', '%' . $location . '%');
        //dd($leads2->get());
        $leads2->Where(function ($query) use ($campaign, $temp) {
            foreach (explode(' ', $campaign->keywords) as $index => $keyword) {
                if ($index == 0) {
                    $temp .= $keyword;
                } else {
                    $temp .= ' ' . $keyword;
                }
                $query->OrWhere('keywords', 'like', '%' . $temp . '%');
            }
        });
$leads2->get();

suggestion :> remove * and type required column only, it will reduce your time — Gaurav Gupta, Jun 05 '19 at 13:31
how large is the table? also this part `or keywords like '%Real Estate%' or keywords like '%Real Estate Agent%'` is redundant. — Samuel Cook, Jun 05 '19 at 13:31
@SamuelCook currently it has 10 million above rows and it will increase more than 100 million and i want relevant data which contains real,real estates, and real estate agent that's why i added ```keywords like '%Real%' or keywords like '%Real Estate%' or keywords like '%Real Estate Agent%'``` — Savan Solanki, Jun 05 '19 at 13:39
Doing a `LIKE %...%` is not going to be fast. Consider using a [fulltext](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) index or using another search provider like elasticsearch. — apokryfos, Jun 05 '19 at 13:51
What @SamuelCook tried to say is: if a keyword is like '%Real Estate%' is also like '%Real%'. In that sense it's redundant — Robert Kock, Jun 05 '19 at 14:15
I suppose that 'keyword' has a limited number of possible values. Instead of storing the keyword itself, you might store an id refering to a table 'keywords'. Then, run the query with `WHERE keyword_id IN (SELECT id FROM keywords WHERE ...)`. Of course, put an index on that column. — Robert Kock, Jun 05 '19 at 14:18

score 2 · Answer 1 · answered Jun 05 '19 at 14:00

This query:

SELECT *
FROM many_leads_lead_details
WHERE location LIKE '%Los%Angeles%'
  AND (keywords LIKE '%Real%' or
       keywords LIKE '%Real Estate%' or
       keywords LIKE '%Real Estate Agent%')

requires a full table scan. Indexes cannot be used because of the wildcards at the beginning of the LIKE patterns.

My first suggestion is to fix your data model. I suspect you are storing multiple values in keywords. These should be in a separate table.

The second suggestion is to use full text search -- the match() function. That is too long a topic for an answer here. You should start with the documentation.

score 1 · Accepted Answer · answered Jun 05 '19 at 21:33

WHERE location LIKE '%Los%Angeles%'
  AND (keywords LIKE '%Real%' or
       keywords LIKE '%Real Estate%' or
       keywords LIKE '%Real Estate Agent%')

-->

WHERE MATCH(location, keywords) AGAINST ('Los Angeles Real Estate Agent')

and have

FULLTEXT(location, keywords)

This will run a lot faster.

score 0 · Answer 3 · answered Jun 05 '19 at 13:44

You have mentioned that the local database returns results quicker. Assuming both databases have similar or same amount of data.

Check if you have indexes the same indexes in both servers.
If there are no difference in indexes, there is a possibility that the remote server is using different query plan. If you find the issue is table scan, you can try to introduce rowcount and pagecount to performance tune:

UPDATE STATISTICS [many_leads_lead_details] WITH ROWCOUNT = 50000000, PAGECOUNT = 500000

Try the Update stats and rerun the query. Hope this helps.

The question is about MySQL/MariaDB; they don't have `UPDATE STATISTICS...` like that. — Rick James, Jun 05 '19 at 21:31

score 0 · Answer 4 · answered Apr 06 '21 at 11:24

use DB facade instead of eloquent query when you select multiple records from database and you don't want relationship with the records returned by the query

When selecting records from the database and you don't need table relationship then it is best to use DB facade over eloquent

Simple Select Query taking too much time in mysql-table with large data-set 10M

4 Answers4