optimize this query
-
select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'
-
select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'
-
Using LIKE, especially with wild cards at the start of a string, basically mean that the database can't use indexes (or can't use them efficiently) so it will naturally take a long time.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Using LIKE, especially with wild cards at the start of a string, basically mean that the database can't use indexes (or can't use them efficiently) so it will naturally take a long time.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'
I wouldn't optimize it! I'd change the data design. You should create another table called Reg_Number_Vehicle_Class that is used to store all of the the different classes you are using. Then create an insert trigger on your premium table to ensure the appropriate class id is assigned to each row. Create a non-unique index on that column and your query should run faster. Chris Meech I am Canadian. [heard in a local bar] The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon] GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
-
select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'
Don't bother; it's a waste of time. You're doing an arbitrary query on text in the middle of a bunch of strings. Short of rearranging your data, the only significant optimization you're going to get is by throwing more hardware at it. What does the schema or the rest of the data in the data set look like? Maybe there's some optimization based on the sequences your're looking for vs. the sequences in the data set. It's unlikely, though.