Re: HT Feature
- Original Message - From: Kaushal Shriyan kaushalshri...@gmail.com Any performance bottleneck if HT (Hyper Threading) is enabled in quad core processors on CentOS 5.8 running MySQL DB Server ? Please help me understand about pros and cons of this feature. That very much depends on your workload. I'd say if you're only running MySQL on that host, chances are that it's going to be beneficial. Best you can do is benchmark with production loads - it varies with exact processor type, architecture, versions and build options of all software, et cetera. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: HT Feature
On Thu, Apr 12, 2012 at 6:38 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Kaushal Shriyan kaushalshri...@gmail.com Any performance bottleneck if HT (Hyper Threading) is enabled in quad core processors on CentOS 5.8 running MySQL DB Server ? Please help me understand about pros and cons of this feature. That very much depends on your workload. I'd say if you're only running MySQL on that host, chances are that it's going to be beneficial. Best you can do is benchmark with production loads - it varies with exact processor type, architecture, versions and build options of all software, et cetera. Hi Johan, Any benchmarking tool you would suggest ? Regards Kaushal -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: The most elegant/efficient way to pull this SQL query
2012/04/11 17:51 -0500, Peter Brawley select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')') from bridge b join people p on b.peopleID=p.peopleID join state s on b.stateID=s.stateID; Since the names are the same in the tables, it works to use USING, too, and you are relieved of the burden of an alias: from bridge join people USING(peopleID) join state USING(stateID) If the fields peopleId and stateID are the only field names in common, NATURAL JOIN also works. from bridge NATURAL join people NATURAL join state -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
My initial goal was to write a very convenient php function that display a table view based on arguments that are super simple to write - without requiring the developer to type-in ( or know ) the ins and outs of joins, natural joins etc. Something like this function showtable($dbh,$table,$fields){ //get the $fields argument parse it out to come up //with one of the SQL statements you guys are discussing. SQL = BuildSQL($table,$fields) //and then use this SQL to output the table } The magic would be happening in the $fields argument. The example in my original question was like this. I'm repeating it for convenience purposes. quote I've got this relational mySQL table that ties peopleIDs from the people table to the states IDs peopleID___stateID 1__1 2__4 3__5 people table is like this; ___peopleID_FName_ ___1joe ___2bob___ ___3charlie_ and finally the state table goes like this; ___stateID_State___ ___1___california ___2___new york ___3___washington__ ___4___texas___ ___5___florida__ What's the most straightforward way to achieve the following view with one SQL statement? peopleID__stateID_ 1_(joe)___1__(california)_ 2_(bob)___4__(texas)__ 3_(charlie)___5__(florida) /quote if the final table ( PeopleAndStates ) view I want were to be as follows; peopleID__stateID_ 1_1___ 2_4___ 3_5___ Then I would have called the function like this; showtable($dbh,$myTable,peopleID,stateID) But if I want to get, the following view instead; peopleID__stateID_ 1_(joe)___1___ 2_(bob)___4___ 3_(charlie)___5___ I would like to be able to call my function as follows; showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID FName),stateID) To mean the following; When you are outputting the peopleID, provide the corresponding Fname field from the PeopleTable where peopleID there is equal to the peopleID you are outputting. What I was seeking from you guys was to find out to most simplistic SQL statement so that when I parse the area with (PeopleTable.PeopleID FName), I can extract the pieces and place it in the final SQL. I'm not sure if you all get the idea of how such a function make debugging super easy. Once you write the parser, you can deploy it over many different cases such as ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on. So, the simpler the SQL, the easier the transition from the starting slate which is really no different than SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates 2012/4/12 Halász Sándor h...@tbbs.net 2012/04/11 17:51 -0500, Peter Brawley select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')') from bridge b join people p on b.peopleID=p.peopleID join state s on b.stateID=s.stateID; Since the names are the same in the tables, it works to use USING, too, and you are relieved of the burden of an alias: from bridge join people USING(peopleID) join state USING(stateID) If the fields peopleId and stateID are the only field names in common, NATURAL JOIN also works. from bridge NATURAL join people NATURAL join state -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/12 11:56 -0700, Haluk Karamete My initial goal was to write a very convenient php function that display a table view based on arguments that are super simple to write - without requiring the developer to type-in ( or know ) the ins and outs of joins, natural joins etc. Something like this function showtable($dbh,$table,$fields){ //get the $fields argument parse it out to come up //with one of the SQL statements you guys are discussing. SQL = BuildSQL($table,$fields) //and then use this SQL to output the table } The magic would be happening in the $fields argument. What's the most straightforward way to achieve the following view with one SQL statement? peopleID__stateID_ 1_(joe)___1__(california)_ 2_(bob)___4__(texas)__ 3_(charlie)___5__(florida) /quote if the final table ( PeopleAndStates ) view I want were to be as follows; peopleID__stateID_ 1_1___ 2_4___ 3_5___ Then I would have called the function like this; showtable($dbh,$myTable,peopleID,stateID) But if I want to get, the following view instead; peopleID__stateID_ 1_(joe)___1___ 2_(bob)___4___ 3_(charlie)___5___ I would like to be able to call my function as follows; showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID FName),stateID) To mean the following; When you are outputting the peopleID, provide the corresponding Fname field from the PeopleTable where peopleID there is equal to the peopleID you are outputting. What I was seeking from you guys was to find out to most simplistic SQL statement so that when I parse the area with (PeopleTable.PeopleID FName), I can extract the pieces and place it in the final SQL. I'm not sure if you all get the idea of how such a function make debugging super easy. Once you write the parser, you can deploy it over many different cases such as ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on. So, the simpler the SQL, the easier the transition from the starting slate which is really no different than SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates (note that in MySQL '' is only right-shift.) I fear that for this function in the end you will need information_schema.COLUMNS. Peter Brawley already gave you a good answer for one of your examples (but I thus would write it): select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, ' (',state,')') AS stateID from people join PeopleAndStates USING(peopleID) join state USING(stateID) The middle example: select peopleID, stateID from PeopleAndStates The last: select concat(peopleID, ' (',fname,')') AS peopleID, stateID from people join PeopleAndStates USING(peopleID) join state USING(stateID) I have assumed that you mean to join only on same-named fields with equality; if not, JOIN ... ON ... is needed. In any case, you have to keep track of it, whether in a result field the table name, too, is needed. If you use USING, for that field leave the table name out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql