Mysql efficient multiple OR requests

Question

I have a query that scans various columns for a search field

SELECT *
FROM $sql_word_query[$count]
WHERE word=\"$word_to_check\"
OR plural=\"$word_to_check\"
OR present_tense=\"$word_to_check\"
OR past_tense=\"$word_to_check\"
OR present_participle=\"$word_to_check\"
OR past_participle=\"$word_to_check\"

The $count is taking a table name from an array.

This is very slow and when I added the last OR part I now get

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/englishw/public_html/app/word.php on line 159

Is there a more efficient way for this scan?

How does $word_to_check fit in with that statement? word, plural, present_tense are all column headers — John Musgrave Bolanos, Jun 06 '13 at 15:19
Firstly, your code is liable to sql injection. That aside, you'd have something like `SELECT * FROM $sql_word_query[$count] WHERE '$work_to_check' in (word, plural, present_tense, past_tense, present_participle, past_participle)` — Aleks G, Jun 06 '13 at 15:23
Can you show us the actual SQL query? How big is the table? What indexes do you have? Can you show the `CREATE TABLE TableName` code? — ypercubeᵀᴹ, Jun 06 '13 at 15:23

fancyPants · Answer 1 · 2013-06-06T15:32:12.210

1

SELECT * 
FROM $sql_word_query[$count] 
WHERE 
\"$word_to_check\" IN (word, plural, present_tense, past_tense, present_participle, past_participle)

Yes, you can swap columns and parameters with IN().

Either

column IN (parameter1, parameter2,...)

or

parameter IN (column1, column2, ...)

The latter is the more convenient way to go in your case, but this does not make the query faster. For this problem we have to see which indexes exist on your table and the result of EXPLAIN SELECT ... .

And be aware that your query is vulnerable to SQL injections.

edited Jun 06 '13 at 15:32

answered Jun 06 '13 at 15:21

fancyPants

49,071
32
84
94

I now have the following working fine: $select."SELECT * FROM $sql_word_query[$count] WHERE \"$word_to_check\" IN (word,plural,present_tense,past_tense,present_participle,past_participle,superlative,comparative)"; - is it possible to remove the FROM $sql_query[$count] array and UNION ALL and explode the array with tables to do a search in all tables with the same parameters? Or am I pushing my luck? – John Musgrave Bolanos Jun 06 '13 at 19:42
the sql commands are directly from the script and there is no user entry for the sql searches - how may I be vulnerable if the script is requesting the data only? – John Musgrave Bolanos Jun 06 '13 at 19:45

score 0 · Answer 2 · answered Jun 06 '13 at 15:21

0

Use the IN syntax:

SELECT * FROM table WHERE word IN('one', 'two', 'three')

If, when you added the last OR clause it started erroring, then you probably don't have a field called past_participle. Check the MySQL error message for more details.

answered Jun 06 '13 at 15:21

MrCode

61,589
10
82
110

Thanks for that - yes, a table had a previous heading and now it is fine. – John Musgrave Bolanos Jun 06 '13 at 19:41

Mysql efficient multiple OR requests

2 Answers2