2

I've written a select statement in mySQL. The duration is 50 seconds, and the fetch is 206 seconds. This is a long time. I'd like to understand WHICH part of my query is inefficient so I can improve its run time, but I'm not sure how to do that in mySQL.

My table has a little over 1,000,000 records. I have an index built in as well:

KEY `idKey` (`id`,`name`),

Here is my query:

SELECT name, id, alt_id, count(id), min(cost), avg(resale), code from
 history where name like "%brian%" group by id;

I've looked at the mySQL Execution Plan, but I can't garner from that what is wrong:

enter image description here

If I highlight over the "Full Index Scan" part of the image, I see this:

Access Type: Index
    Full Index Scan
Key/Index:
   Used Key Parts: id, name
   Possible Keys: idKey, id-Key, nameKey
Attach Condition:
   (`allhistory`.`history`.`name` LIKE '%brian%')

Rows Examined Per Scan: 1098181
Rows Produced Per Join: 1098181
Filter: 100%

I know I can just scan a smaller subset of data by adding a LIMIT 100 into the query, and while it makes the time much shorter, (28 second duration, 0.000 sec Fetch,) I also want to see all the records - so I don't really want to put a limit on it.

Can someone more knowledgeable on this topic suggest where my query, my index, or my methodology might be inefficient for what I'm trying to accomplish?

Brian Powell
  • 3,336
  • 4
  • 34
  • 60
  • 1
    `like "%brian%" ` full table/index scan. Reason: **NON-SARGABLE** expression. Think about phone book it is easy to find `brain%`, go to page B - C and search. With `%brain%` search entire phone book it will take ages :D – Lukasz Szozda Oct 15 '15 at 17:36
  • Does it have to be `%brian%`? If there is an index on `name`, it could be used if the search were `brian%`. – wallyk Oct 15 '15 at 17:38
  • Oh ok - so by using the wildcard I force the search to ignore my index - is that what "full table scan" means? – Brian Powell Oct 15 '15 at 17:39
  • 1
    Using `%` at begining you force to full index scan, but still you can use wildcard at the end – Lukasz Szozda Oct 15 '15 at 17:40
  • perhaps you can embrace the potential power and likely misery of mysql [FULLTEXT search](https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) – Drew Oct 15 '15 at 17:41
  • The thing is - I need to allow my users to search `by keyword` not just be unique record number or something definite. There has to be a way for me to be able to do that without having a 6 minute execution time.... – Brian Powell Oct 15 '15 at 17:42
  • check out my answer [here](http://stackoverflow.com/a/30677347) for a 2 minute view of full text search – Drew Oct 15 '15 at 17:44
  • WHY would someone downvote? If this isn't an example of what Stackoverflow itself considers a good question: http://stackoverflow.com/help/how-to-ask I don't know what is........ – Brian Powell Oct 15 '15 at 17:44
  • @Drew Let me test it out and see how it works :) – Brian Powell Oct 15 '15 at 17:47
  • 1
    Brian, some times you have to deal with people in rotten moods. Can one find a more opinionated lot than programmers? :) – Drew Oct 15 '15 at 17:51

2 Answers2

1

I'm thinking your covered index may be backwards. Try switching the order (name, id). That way the WHERE clause can take advantage of the index.

Joel
  • 1,579
  • 3
  • 12
  • 19
1

This question has a solution only in mysql full text search functionality.

I don't consider the use of like a workable solution. Table scans are not a solution with millions of rows.

I wrote up an answer in this link, I hope you find a workable solution for yours with that reference and quick walk thru.

Here is one of the Mysql Manual Pages on Full Text Search.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Does my inclusion of a `like` statement automatically force a full table scan? Does the term `full table scan` mean that any index I've created is being ignored? Sorry for the noob questions... – Brian Powell Oct 15 '15 at 17:51
  • the first comment under your question from @lad2025 describes it well. As for your second question, it the db engine has to perform a full table scan, it means it tried to use indexes in place, and abandoned such fruitful efforts, and plods thru every row – Drew Oct 15 '15 at 17:52
  • check out [mysql explain syntax](https://dev.mysql.com/doc/refman/5.0/en/explain.html). It won't run the query (with explain plopped in front of it). It will show you the db plan quickly – Drew Oct 15 '15 at 17:55
  • Beautiful. This really helps me understand. Thank you Drew! – Brian Powell Oct 15 '15 at 18:00
  • 1
    you're welcome. Good luck. Results will come back fast. The *Quality* of the results is yet to be determined ! – Drew Oct 15 '15 at 18:02