better way to structure table / write a query?

[michaellunsford]

SELECT * FROM `table1`,`table2` WHERE `table1`.`userid` = `table2`.`keyfield` LIMIT 30

 

they keyfield in `table1` and `table2` have the same name, so the database returns two identically named fields with different values. Should I rename one of the keyfields in the database, or is there a way to write the query to rename one of them without having to call every single other field?

 

for example, I can rename the field like so, but it will only return the keyfield from the second table:

 

SELECT `table1`.*, `table2`.`keyfield` AS `userkey` FROM `table1`,`table2` WHERE `table1`.`userid` = `table2`.`keyfield` LIMIT 30

 

To get around it, I'd have to add `table2`.`name`, `table2`.`address`, `table2`.`phone`, ...etc to the select portion of the query -- making it rather unwieldy.

 

I run into this kind of problem occasionally, and permanently renaming one of keyfields in one of the tables would 'fix' the problem. However, I'm hoping there's another way (so I don't have to go back and debug a bunch of other queries across the whole site).

 

Thoughts?

Edited December 20, 2013 by michaellunsford

[Barand]

Just use a column alias in the query. No need to rename them in the database.

[kicken]

To get around it, I'd have to add `table2`.`name`, `table2`.`address`, `table2`.`phone`, ...etc to the select portion of the query -- making it rather unwieldy.

You should be listing out your columns anyway and not using * at all. For a few reasons, namely:

 

1) The less data you select the better. The DB may be able to better optimize the query or spend less time gathering results if it has less columns to fetch. Also less time/memory is wasted transferring the result back to the application. Select only what you need

2) Listing the columns means you know just by looking at the query what columns are available in the results and what their names are. By using * you have to either memorize that or always refer back to the table structures.

 

The ` are not necessary unless you are attempting to use a reserved word. If you are, I would recommend you change the column name rather than work around it with backticks. Not having to constantly quote everything makes it much nicer when writing queries out.

[michaellunsford]

Thanks for the quick replies. I suppose you guys are right. Now, I had thought the * was easier on the database than a long query with a score of field names written out. Not so?

 

speaking of tedium, back-ticking everything is automatic, so it just goes in every query I write.

[kicken]

Now, I had thought the * was easier on the database than a long query with a score of field names written out. Not so?

Parsing the query text is easy and barely takes any time at all. Executing the query and fetching the requested data is where all the time is spent when running a query. The fewer fields you interact with, the better job the database can do in optimizing the query for execution and data fetching.

 

Say for example you had a table like this:

create table blog_posts (
   postId int auto_increment primary key
   , postDate datetime
   , title varchar(1000)
   , postContent text
   , authorId int
   , index (postDate, title)
)

And then on a page you query the table like so:

select * from blog_posts where postDate between '2013-1-1' and '2013-2-1' order by postDate

And say the page only lists out the titles and dates of the post with a link to each, meaning all you need is the title, postId, and postDate fields

 

By using *, mysql will have to not only pull the needed fields, but also the extra postContent and author fields. Now the author field is probably not a big deal, it's just an int stored with the rest of the data in the row. The postContent field however could be a very large amount of text, and is not stored along side the main row data which means mysql would have to do some extra seeking and jumping around to read out that field, wasting time. All that extra data is then transferred to your app taking up bandwidth and more time, just to be completely ignored.

 

So, lets rewrite the query with only what we need

select postId, title, postDate from blog_posts where postDate between '2013-1-1' and '2013-2-1' order by postDate

Now mysql will only fetch those three items, not wasting time and resources looking up postContent data. As an added bonus, because the necessary fields are part of an index, mysql can just pull their values directly from the index as it searches for the posts rather than having to refer back to the data table after it has found the required posts. Sending the results back to your app will go much quicker as there is a lot less data to be transferred, and your app will use up less memory not having to read in unnecessary result data.

[Barand]

Not only is it more efficient, it is also a whole lot better for you (or anyone else) reading the query at a later date as it helps to document the query. You can now see exactly what data the query needs to extract.

[michaellunsford]

Thanks, guys. I'll go in and rewrite the query to include the alias and all the other fields I need for the summary page.