0

I have a search setup that I'm trying to optimize a query for using MySQL ver14.14 5.5.36 x64_64 and php version is 5.2.17.

There are a couple hundred thousand items in each table within the database and the time to complete the search is getting intolerable when there are 50+ users online.

Basically, I loop through the tables in a database using:

$result =  mysql_query("show tables",$dbc1); 
while($table = mysql_fetch_array($result)) {
$tablename = $table[0];

where I would then execute this query:

"SELECT * FROM $tablename WHERE FirstName like '$SanitizedFirstName%' and LastName like '$SanitizedLastName%' order by ID asc "

and then I loop through and output the results 

while($row = mysql_fetch_array( $resultant2 )) {

Here is a sample of the database and some tables in it

database:people

table:congress

| ID | FirstName | LastName |
| 111111111 | Joe | SkypeMan |
| 111111112 | Susan | Yahooser |
| 111111113 | Bill | Googleheim |
| 111111114 | Margaret | Bingledorf |
| 111111115 | Mark | Aoldelson |

table:senate

| ID | FirstName | LastName |
| 111111111 | Tod | Sampleguy |
| 111111112 | Jason | Datafill |
| 111111113 | Neff | LoremIpsum |
| 111111114 | William | Dotdotdot |
| 111111115 | Lisa | Commaquote |

any help on optimizing this would be appreciated!

Query
  • 1
  • 1
  • 1
    Please be aware that the mysql extension has been deprecated and it's use has been discouraged for years now. You might want to migrate to mysqli or PDO, also see http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Oldskool May 13 '14 at 20:04
  • 1
    Combine all the different tables into one, adding an identifier for 'senate', 'congress', etc. Then you need just one query instead of several. –  May 13 '14 at 20:07
  • Don't use "SELECT *". You should only retrieve the fields you are actually going to use. EX: "SELECT ID, FirstName, LastName..." – rrtx2000 May 13 '14 at 20:15
  • thanks, I'm aware it's depreciated. Combining all into one is not possible. I'm in the process of rewriting the select statements. Thank you all for your input! – Query May 17 '14 at 22:12

2 Answers2

1

Get rid of LIKE and use = if at all possible in the WHERE clause. This will likely solve your problem. It would help to see your indexes as well. I presume you have FirstName and LastName indexed.

mseifert
  • 5,390
  • 9
  • 38
  • 100
  • I changed like to = , which did'nt help much, and am I'm in the process of setting up FirstName and LastName indexes. Thank You! – Query May 17 '14 at 22:14
  • You need indexes on each field of a `WHERE` clause. Using `%` at the beginning of the query value (e.g. LastName LIKE '%$SanitizedLastName%') prevents the use of the index for that condition. However, using it as you have without the leading `%` (e.g. LastName LIKE '$SanitizedLastName%') may allow you to use `LIKE` and achieve the performance you need. It is still better to use `=` when you can. Let me know how it goes. – mseifert May 18 '14 at 19:40
0

Converting the LIKES to '=' will really help with the speed, but as long as you keep the percentage sign ONLY at the back, you're OK. If it's really running slow, you should make sure that you add an INDEX on the columns that you use in the WHERE statements. Adding an index is as easy as:

CREATE INDEX index_name
ON table_name (column_name)
user2704687
  • 185
  • 3
  • 11