3

Related to this question:

Wildcards in column name for MySQL

Basically, there are going to be a variable number of columns with the name "word" in them.

What I want to know is, would it be faster to do a separate database call for each row (via getting the column information from the information schema), with a generated Python query per row, or would it be faster to simply SELECT *, and only use the columns I needed? Is it possible to say SELECT * NOT XYZ? As far as I can tell, no, there is no way to specifically exclude columns.

There aren't going to be many different rows at first - only three. But there's the potential for infinite rows in this. It's basically dependent on how many different types of search queries we want to put on our website. Our whole scalability is based around expanding the number of rows.

Community
  • 1
  • 1
Steven Matthews
  • 9,705
  • 45
  • 126
  • 232
  • In your question, do you mean "columns" when you say "rows"? Otherwise, I cannot quite see what you mean. – martin Jul 13 '12 at 09:04
  • No, I'm doing something of an odd database design, as far as I can tell - if you have a better suggestion, happy to hear it. I'm setting up multiple columns for each separate word, because all of the words are going to be associated with another column, with a value. All of those words are then going to be combined into a strong, with the value being associated in an array or dictionary and then this will be passed onto another function. – Steven Matthews Jul 13 '12 at 09:07
  • 1
    You're designing your database completely wrong. – vartec Jul 13 '12 at 10:24
  • I am quite worried about that myself. – Steven Matthews Jul 13 '12 at 10:26
  • Well, without any enlightenment, I can't really do anything here. – Steven Matthews Jul 13 '12 at 10:35

1 Answers1

1

If all you are doing is limiting the number of columns returned there is no need to do a dynamic query. The hard work for the database is in selecting the rows matching your WHERE clause; it makes little difference to send you 5 columns out of 10, or all 10.

Just use a "SELECT * FROM ..." and use Python to pick out the columns from the result set. You'll use just one query to the database, so MySQL only has to work once, then filter out your columns:

cursor.execute('SELECT * FROM ...')
cols = [i for i, col in enumerate(cursor.description) if col[0].startswith('word')]
for row in cursor:
    columns = [row[c] for c in cols]

You may have to use for row in cursor.fetchall() instead depending on your MySQL python module.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • But won't that get extra columns than what I need? I was taught that that was inefficient. Though I'll be honest, I see no more efficient way to do this, without the potential of very hack-y code that might well end up far more /inefficient/ – Steven Matthews Jul 13 '12 at 09:08
  • 1
    Inefficiency is relative. Multiple database queries are more inefficient for example. Parsing a SQL query and executing is (far) more expensive than selecting a subset of columns to send. Unless you are limiting yourself to 3 out of of a 1000 columns, selecting all of them and limiting in python is going to win. – Martijn Pieters Jul 13 '12 at 09:09
  • Ok, you've made your argument sufficiently for me. I'm just trying to write the cleanest code I ever have for this project. – Steven Matthews Jul 13 '12 at 09:12
  • results = c.execute("""SELECT * from searchtest where onstate = 1;""") print results.description Gets this error: AttributeError: 'long' object has no attribute 'description' – Steven Matthews Jul 13 '12 at 09:55
  • 1
    Sorry, that should have been `cursor.description`; see the [Python DB API spec 2.0](http://www.python.org/dev/peps/pep-0249/) – Martijn Pieters Jul 13 '12 at 09:58
  • Getting a typeerror: TypeError: 'long' object is not iterable – Steven Matthews Jul 13 '12 at 10:04
  • Ick; no, iterate over the cursor instead, *or* use cursor.fetchall(); the return value of `.execute()` is undefined and in your case probably the number of rows returned. Sorry. – Martijn Pieters Jul 13 '12 at 10:07
  • c.execute("""SELECT * from searchtest where onstate = 1;""") print c.description cols = [i for i, col in enumerate(c.description) if col[0].startswith('word')] results = (c.fetchall()) for row in results: print row This seems to work (using prints to show off information, obviously) – Steven Matthews Jul 13 '12 at 10:11
  • thank you, I THINK I've got the structure I need to actually do this! – Steven Matthews Jul 13 '12 at 10:14
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13840/discussion-between-andrew-alexander-and-martijn-pieters) – Steven Matthews Jul 13 '12 at 10:45