Search Results

Search found 34274 results on 1371 pages for 'mysql table'.

Page 142/1371 | < Previous Page | 138 139 140 141 142 143 144 145 146 147 148 149  | Next Page >

• I am looking for good free reporting tool for MYSQL databases (web or windows)

  - by Software Enthusiastic

  Hi Can anyone please suggest me a good free mysql based reporting tool which works either on web or windows? Thanks and Regards

  Read the article

• mysql report sql help

  - by sfgroups

  I have mysql table with data like this. record will have server with total cpu and virtual server with cpu assinged type, cpu srv1, 10 vsrv11, 2 vsrv12, 3 srv2, 15 vsrv21, 6 vsrv22, 7 vsrv23, 1 from the above data, I want to create output like this. server, total cpu, assigned cpu, free cpu srv1, 10, 5, 5 srv2, 15, 14, 1 Can you help me on creating sql query for this report? I have changed my table and data like this. CREATE TABLE `cpuallocation` ( `servertype` varchar(10) DEFAULT NULL, `servername` varchar(20) DEFAULT NULL, `hostname` varchar(20) DEFAULT NULL, `cpu_count` float DEFAULT NULL, UNIQUE KEY `server_uniq_idx` (`servertype`,`servername`,`hostname`) insert into cpuallocation values('srv', 'server1', '',16); insert into cpuallocation values('vir', 'server1', 'host1',5); insert into cpuallocation values('vir', 'server1', 'host2',2.5); insert into cpuallocation values('vir', 'server1', 'host3',4.5); insert into cpuallocation values('srv', 'server2', '',8); insert into cpuallocation values('vir', 'server2', 'host1',5); insert into cpuallocation values('vir', 'server2', 'host2',2.5); insert into cpuallocation values('srv', 'server3', '',24); insert into cpuallocation values('vir', 'server3', 'host1',12); insert into cpuallocation values('vir', 'server3', 'host2',2); insert into cpuallocation values('srv', 'server4', '',12); Update: I created two view, now I getting the result I want. create view v1 as select servername, sum(cpu_count) as cpu_allocated from cpuallocation where servertype='vir' group by servername; create view v2 as select servername, cpu_count as total_cpu from cpuallocation where servertype='srv'; select a.servername, a.total_cpu, b.cpu_allocated from v2 as a left join v1 as b on a.servername=b.servername; +------------+-----------+---------------+ | servername | total_cpu | cpu_allocated | +------------+-----------+---------------+ | server1 | 16 | 12 | | server2 | 8 | 7.5 | | server3 | 24 | 14 | | server4 | 12 | NULL | +------------+-----------+---------------+ 4 rows in set (0.00 sec) Is it possible to create a query with-out creating views?

  Read the article

• Problems with mysql syntax

  - by user368453

  Hello everyone !! I´m trying to create a trigger on MySQL but I´m having a sintaxe problem, which I was not able to find. If someone more experient could help me it would be great (it´s the first time I use MySQL!)... The reason why I´m creating this trigger is for deleting all the orphan "labels", which has a many-to-many relation with "service_descriptor" (this two entities are linked by service_labels). The code I have is: CREATE TRIGGER trg_delete_orphan_label AFTER DELETE FOR EACH ROW ON restdb.service_labels DELETE FROM restdb.labels WHERE EXISTS (SELECT * FROM old D LEFT_JOIN restdb.service_labels SL ON SL.id_label = D.id_label AND D.id_service = SL.id_service WHERE SL.id_label IS NULL restdb.labels.id = D.SL.id_label ); Thanks in advance !

  Read the article

• Updating cached counts in MySQL

  - by phleet

  In order to fix a bug, I have to iterate over all the rows in a table, updating a cached count of children to what its real value should be. The structure of the things in the table form a tree. In rails, the following does what I want: Thing.all.each do |th| Thing.connection.update( " UPDATE #{Thing.quoted_table_name} SET children_count = #{th.children.count} WHERE id = #{th.id} " ) end Is there any way of doing this in a single MySQL query? Alternatively, is there any way of doing this in multiple queries, but in pure MySQL? I want something like UPDATE table_name SET children_count = ( SELECT COUNT(*) FROM table_name AS tbl WHERE tbl.parent_id = table_name.id ) except the above doesn't work (I understand why it doesn't).

  Read the article

• Determining difference in timestamps for two values in the same MySQL table

  - by JayRizzo03

  I am relatively new to programming in PHP, so I apologize if this is a rather simple question. I have a MySQL database table called MachineReports that contains the following values: ReportNum(primary key, auto increment), MachineID and Timestamp Here is some example data: |ReportNum | MachineID | Timestamp | |1 | AD3203 | 2012-11-18 06:32:28| |2 | AD3203 | 2012-11-19 04:00:15| |3 | BC4300 | 2012-11-19 04:00:15| What I am attempting to do is find the difference in timestamps in seconds for each machine ID by iterating over each row set. I am getting stuck on the best way to do this, however. Here is the code I've written so far: <?php include '../dbconnect/dbconnect.php'; $machineID=[]; //Get a list of all MachineIDs in the database foreach($dbh->query('SELECT DISTINCT(MachineID) FROM MachineReports') as $row) { array_push($machineID, $row[0]); } for($i=0;$i<count($machineID);$i++){ foreach($dbh->query("SELECT MachineID FROM MachineReports WHERE MachineID='$machineID[$i]' ORDER BY MachineID") as $row) { //code to associate each machineID with two time stamps goes here } } ? This code just lists out the contents of the table row by row. My ultimate goal is to find the difference in timestamps for a certain MachineID. One of the things I've considered is using a multidimensional array in php - using the $machineID as the key and then storing the timestamp inside the array the key points to. However, I'm uncertain how to do that since my query parses row by row. I have quite a few questions. 1) Is this the most efficient way to be doing this? I suspect my database table design may not be the best. 2)What would be the best way to determine the difference in timestamps for a certain machineID? Even just a pointer to a topic that would prompt me to think about this in a different way would be helpful - I'm not afraid to do research. Thanks!

  Read the article

• MySQL Table Loop using PHP

  - by JM4

  I have an online form which collects consumer data and stores in a dedicated MySQL database. In some instances, data is passed in the URL under the "RefID" variable which is also stored in the database and attached to each registration. I use the 'mysql_num_rows ($result)' to fetch all agent details on another page but this only returns ALL available details. My goal is as follows: GOAL I want to create an HTML table in which rows are automatically generated based on the list of all registrations on my site. A new row is created IF and ONLY IF a unique RefID is present on that particular record. In the event the field is NULL, it is reported on a single line. In short, the HTML table could look something like this: RefID - Number of Enrollments abc123 - 10 baseball - 11 twonk - 7 NULL - 33 Where abc123 is a particular RefID and 10 is the number of times that RefID appears in the DB. If a new registration comes in with RefID = "horses", a new row is created, showing "horses - 1". The HTML table will be viewable by account administrators needing to see the number of enrollments for a particular RefID (which they won't know ahead of time). Anybody have any suggestions?

  Read the article

• Getting deadlocks in MySQL

  - by at

  We're very frustratingly getting deadlocks in MySQL. It isn't because of exceeding a lock timeout as the deadlocks happen instantly when they do happen. Here's the SQL code that is executing on 2 separate threads (with 2 separate connections from the connection pool) that produces a deadlock: UPDATE Sequences SET Counter = LAST_INSERT_ID(Counter + 1) WHERE Sequence IS NULL Sequences table has 2 columns: Sequence and Counter The LAST_INSERT_ID allows us to retrieve this updated counter value as per MySQL's recommendation. That works perfect for us, but we get these deadlocks! Why are we getting them and how can we avoid them?? Thanks so much for any help with this.

  Read the article

• mysql_close(): supplied argument is not a valid MySQL-Link resource

  - by maxedison

  I'm trying to get the hang of using custom session handlers to store session data in a MySQL database. However, I keep getting the following warning: mysql_close(): supplied argument is not a valid MySQL-Link resource Here's the code I'm using, which I got from here: function _open(){ global $_sess_db; $_sess_db = mysql_connect("localhost", "root", "******"); if ($_sess_db) { return mysql_select_db('style', $_sess_db); } return false; } function _close(){ global $_sess_db; return mysql_close($_sess_db); //error happens here } The full text of the error message ultimately points to the final "return mysql_close($_sess_db);" line. I can confirm that the mysql_connect info does in fact work, and I do have the rest of the session handler functions defined as well. And in case it helps, I get these errors immediately upon page load, without actually calling any of the session handler functions, and without having any current sessions open.

  Read the article

• STR_TO_DATE parsing in mysql

  - by wo_shi_ni_ba_ba

  trying to parse "06/01/2010 15:00:00 08:00" the problem is the last offset hour, mysql date_time can't parse it, any idea?

  Read the article

• mysql querying age from dob

  - by Confused

  My MySQL database has date of birth stored in the format yyyy-mm-dd, how do I query the database - without using php code - to show me results of people born within x years of now, and/or, who are y years of age? The table is called users, the colum is called dob. I've spent a while viewing the MySQL manual but I can't work out precisely how to form my query. So far I went for select * from users where dob... but I don't think that's the way to go.

  Read the article

• Way to store dates with missing days/months in mysql (php)

  - by Paul M

  I want to store dates in mysql so I set the table to be of type 'Date', which is fine but mysql requires that the full date is provided YYYY-MM-DD. However some of my data does not include day and some is missing the month. Now I could just use a varchar(10) field, but then its difficult to run date based queries on the data. Is there another date format which is not as strong and would allow me to use ?? or 00 where the day/month is not known?

  Read the article

• PGDATA equivalence for MySQL

  - by Kamelito

  Is there an equivalence of ($)PGDATA (Postgres) for MySQL?

  Read the article

• insert into select from other table

  - by user3815079

  I need to add multiple records based on data from another table where the event is the same. I've found on this forum insert into table2(id,name) select "001",first_name from table1 where table1.id="001" as possible solution for my question. So I thought this should be the following syntax: insert into reservations(event,seat) select "99",id from seats where seats.id>0 to add all seats to event 99. However when I run this query mysql gives the message 'MySQL returned an empty resultset (0 rows). (query 0.0028 sec)' and no records were added. I translated the message so could be sligthly different. When I only use the "select "99",id from seats where seats.id0" query, it returns me 1080 rows.

  Read the article

• best way of importing data into mysql

  - by every_answer_gets_a_point

  i have a table with 500,000 rows and about 10 columns that is in access how do i import it into mysql?

  Read the article

• Get the Latest on MySQL Enterprise Edition

  - by monica.kumar

  Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; mso-themecolor:hyperlink; text-decoration:underline; text-underline:single;}a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color:purple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;}p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}.MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}.MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;}@page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;}div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:595597020; mso-list-type:hybrid; mso-list-template-ids:1001697690 67698689 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}@list l0:level1 {mso-level-number-format:bullet; mso-level-text:?; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-.25in; font-family:Symbol;}ol {margin-bottom:0in;}ul {margin-bottom:0in;}--Oracle just announced MySQL 5.5 Enterprise Edition. MySQL Enterprise Edition is a comprehensive subscription that includes:- MySQL Database- MySQL Enterprise Backup- MySQL Enterprise Monitor- MySQL Workbench- Oracle Premier Support; 24x7, WorldwideNew in this release is the addition of MySQL Enterprise Backup and MySQL Workbench along with enhancements in MySQL Enterprise Monitor. Recent integration with MyOracle Support allows MySQL customers to access the same support infrastructure used for Oracle Database customers. Joint MySQL and Oracle customers can experience faster problem resolution by using a common technical support interface. Supporting multiple operating systems, including Linux and Windows, MySQL Enterprise Edition can enable customers to achieve up to 90 percent TCO savingsover Microsoft SQL Server. See what Booking.com is saying:“With more than 50 million unique monthly visitors, performance and uptime are our first priorities,” said Bert Lindner, Senior Systems Architect, Booking.com. “The MySQL Enterprise Monitor is an essential tool to monitor, tune and manage our many MySQL instances. It allows us to zoom in quickly on the right areas, so we can spend our time and resources where it matters.” Read the press release for detailson technology enhancements.

  Read the article

• MySQL - Installation

  - by Stuart Brierley

  In order to create a development environment for a project I am working on, I recently needed to install MySQL Server.  The first step was to download the msi. Running this presents you with the installer splash screen, detailing the version of MySQL that you are about to install - in this case MySQL Server 5.1. Next you can choose whether to install a Typical, Complete or Custom installation.  Although this is the first time I have installed MySQL and the Custom option states "Recommended for advanced users" I opted to carry out a Custom installation, specifically so that I could be sure of what features and components were installed. On the Custom Setup screen you can choose which components to install.  By default the Developer Components are not included, but I opted to include some of these elements. Next up is the ready to install screen and then the intsallation progress.   Following the completion of the installation you are shown a few screens with details of the MySQL Enterprise subscription option. Finally the installation is complete and you are offered the chance to configure and register MySQL. Next I will be looking at the configuration of MySQL Server 5.1.

  Read the article

• Inserting THEAD element into embedded HTML using jQuery

  - by robalot

  I'm trying to use jQuery to insert HTML into a table element. I've been messing variations of the selector (below) with no luck. Can someone help me? My Selector: $j('#ctl00_body_gridData_dom').children('table:first').append("<thead><tr><td colspan='6'>&nbsp;</td><td align='center' colspan='7'>EM SPECS</td><td align='center' colspan='7'>FISH</td><td colspan='11'>&nbsp;</td></tr></thead>"); Here's what I am trying to do... I want to insert this: <thead> <tr> <td colspan="6"> &nbsp; </td> <td align="center" colspan="7"> EM SPECS </td> <td align="center" colspan="7"> FISH </td> <td colspan="11"> &nbsp; </td> </tr> </thead> The sample below is what I want the end result to look like... So It Looks Like This: Jquery Event Pool <table id="ctl00_body_gridData" style="width: 2000px; -moz-user-select: none;" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr> <td id="ctl00_body_gridData_dom" class="GridData" style="vertical-align: top; height: 245px;" valign="top"> <table style="width: 100%;" border="0" cellpadding="0" cellspacing="0"> <thead> <tr> <td colspan="6"> &nbsp; </td> <td align="center" colspan="7"> EM SPECS </td> <td align="center" colspan="7"> FISH </td> <td colspan="11"> &nbsp; </td> </tr> </thead> <tbody> <tr id="ctl00_body_gridData_top_head" class="headerlineGrid"> <td width="16"> <div style="width: 16px;"></div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,4,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,4,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,0,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,4,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,4,0)" style="width: 89px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 89px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Work<br>Package</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,6,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,6,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,1,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,6,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,6,0)" style="width: 62px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 62px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Work<br>Order</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,9,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,9,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,2,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,9,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,9,0)" style="width: 66px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 66px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> FCR<br>Group</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,12,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,12,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,3,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,12,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,12,0)" style="width: 105px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 105px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center">Contractor</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,15,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,15,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,4,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,15,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,15,0)" style="width: 159px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 159px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Capital/Expense<br>Group</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,19,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,19,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,5,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,19,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,19,0)" style="width: 99px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 99px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center">Cost Type</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,20,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,20,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,6,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,20,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,20,0)" style="width: 81px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 81px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Commit<br>Dollars</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,21,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,21,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,7,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,21,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,21,0)" style="width: 81px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 81px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Commit<br>Hours</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,22,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,22,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,8,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,22,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,22,0)" style="width: 86px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 86px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Commit<br>Quantity</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,23,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,23,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,9,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,23,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,23,0)" style="width: 76px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 76px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Control<br>Budget</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,24,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,24,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,10,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,24,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,24,0)" style="width: 46px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 46px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center">FTC</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,25,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,25,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,11,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,25,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,25,0)" style="width: 88px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 88px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Total<br>Forecast</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,26,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,26,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,12,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,26,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,26,0)" style="width: 50px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 50px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br>COB</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,27,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,27,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,13,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,27,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,27,0)" style="width: 49px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 49px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br>CCB</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,28,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,28,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,14,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,28,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,28,0)" style="width: 81px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 81px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br> Commit<br>$</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,29,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,29,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,15,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,29,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,29,0)" style="width: 81px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 81px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br> Commit<br>Hours</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,30,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,30,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,16,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,30,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,30,0)" style="width: 86px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 86px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br> Commit<br>Quantity</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,31,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,31,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,17,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,31,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,31,0)" style="width: 95px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 95px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Ctr<br>% Compl.</td> </tr> </tbody> </table> </div> </td> <td onclick="ctl00_body_gridData.ClickHandler(event,this,32,0)" ondblclick="ctl00_body_gridData.DblClickHandler(event,null,32,0)" onmousemove="ctl00_body_gridData.MoveHandler(event,this,18,0)" onmouseover="ctl00_body_gridData.OverHandler(event,this,0)" onmouseout="ctl00_body_gridData.OutHandler(event,this,0)" onmousedown="ctl00_body_gridData.DownHandler(event,this,32,0)" onmouseup="ctl00_body_gridData.UpHandler(event,this,32,0)" style="width: 105px;" class="HeadingCell" align="center"> <div style="text-align: center; overflow: hidden; width: 105px;"> <table width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="white-space: nowrap; text-align: center;" class="HeadingCellText" align="center"> Contractor<br>CFTC</td> </tr> </tbody> </table> </div> </td> </tr> <tr> <td id="ctl00_body_gridData_expcolgrp_0" width="16" align="center"></td> <td class="GroupHeading" colspan="20"> FCR<br>Group: Engineering</td> </tr> <tr> <td id="ctl00_body_gridData_expcolgrp_1" width="16" align="center"></td> <td class="GroupHeading" colspan="20"> FCR<br>Group: Pipe</td> </tr> <tr> <td id="ctl00_body_gridData_expcolgrp_2" width="16" align="center"></td> <td class="GroupHeading" colspan="20"> FCR<br>Group: Concrete</td> </tr> <tr> <td id="ctl00_body_gridData_expcolgrp_3" width="16" align="center"></td> <td class="GroupHeading" colspan="20"> FCR<br>Group: Insulation</td> </tr> <tr> <td id="ctl00_body_gridData_expcolgrp_4" width="16" align="center"></td> <td class="GroupHeading" colspan="20"> FCR<br>Group: Buildings</td> </tr> </tbody> </table> </td> </tr> </tbody> </table> </body> </html>

  Read the article

• MySQL Query performance - huge difference in time

  - by Damo

  I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped both of these databases to my local machine where I can reproduce the issue running MySQL 5.1.37. Curiously, database B is smaller than database A. A stripped down version of the query that reproduces the problem is: SELECT * FROM po_shipment ps JOIN po_shipment_item psi USING (ship_id) JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items JOIN po_header ph ON pa.hdr_id = ph.hdr_id LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' WHERE ps.eta >= '2010-03-22' GROUP BY ps.ship_id LIMIT 100; The EXPLAIN query plan for the first database (A) that returns in ~2 seconds is: +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 174 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_PROD.ps.ship_id | 1 | | | 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment1 | 4 | UNIVIS_PROD.psi.ship_id | 5 | Using where | | 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_PROD.pa.hdr_id | 1 | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ The EXPLAIN query plan for the second database (B) that returns in 600 seconds is: +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 38 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_DEV01.ps.ship_id | 1 | | | 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | | | 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2 | 4 | UNIVIS_DEV01.ps.ship_id | 4 | Using where | | 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_DEV01.pa.hdr_id | 1 | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ When database B is running I can look at the MySQL Administrator and the state remains at "Copying to tmp table" indefinitely. Database A also has this state but for only a second or so. There are no differences in the table structure, indexes, keys etc between these databases (I have done show create tables and diff'd them). The sizes of the tables are: database A: po_shipment 1776 po_shipment_item 1945 po_alloc 36298 po_header 71642 EVENT_TABLE 1608 database B: po_shipment 463 po_shipment_item 470 po_alloc 3291 po_header 56149 EVENT_TABLE 1089 Some points to note: Removing the WHERE clause makes the query return < 1 sec. Removing the GROUP BY makes the query return < 1 sec. Removing ev5, ev4, ev3 etc makes the query get faster for each one removed. Can anyone suggest how to resolve this issue? What have I missed? Many Thanks.

  Read the article

• Mysql - Help me change this single complex query to use temporary tables

  - by sandeepan-nath

  About the system: - There are tutors who create classes and packs - A tags based search approach is being followed.Tag relations are created when new tutors register and when tutors create packs (this makes tutors and packs searcheable). For details please check the section How tags work in this system? below. Following is the concerned query Can anybody help me suggest an approach using temporary tables. We have indexed all the relevant fields and it looks like this is the least time possible with this approach:- SELECT SUM(DISTINCT( t.tag LIKE "%Dictatorship%" OR tt.tag LIKE "%Dictatorship%" OR ttt.tag LIKE "%Dictatorship%" )) AS key_1_total_matches , SUM(DISTINCT( t.tag LIKE "%democracy%" OR tt.tag LIKE "%democracy%" OR ttt.tag LIKE "%democracy%" )) AS key_2_total_matches , COUNT(DISTINCT( od.id_od )) AS tutor_popularity, CASE WHEN ( IF(( wc.id_wc > 0 ), ( wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date > '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'classes_published' , CASE WHEN ( IF(( lp.id_lp > 0 ), ( lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'packs_published', td . *, u . * FROM tutor_details AS td JOIN users AS u ON u.id_user = td.id_user LEFT JOIN learning_packs_tag_relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor LEFT JOIN learning_packs AS lp ON lptagrels.id_lp = lp.id_lp LEFT JOIN learning_packs_categories AS lpc ON lpc.id_lp_cat = lp.id_lp_cat LEFT JOIN learning_packs_categories AS lpcp ON lpcp.id_lp_cat = lpc.id_parent LEFT JOIN learning_pack_content AS lpct ON ( lp.id_lp = lpct.id_lp ) LEFT JOIN webclasses_tag_relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN webclasses AS wc ON wtagrels.id_wc = wc.id_wc LEFT JOIN learning_packs_categories AS wcc ON wcc.id_lp_cat = wc.id_wp_cat LEFT JOIN learning_packs_categories AS wccp ON wccp.id_lp_cat = wcc.id_parent LEFT JOIN order_details AS od ON td.id_tutor = od.id_author LEFT JOIN orders AS o ON od.id_order = o.id_order LEFT JOIN tutors_tag_relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor LEFT JOIN tags AS t ON t.id_tag = ttagrels.id_tag LEFT JOIN tags AS tt ON tt.id_tag = lptagrels.id_tag LEFT JOIN tags AS ttt ON ttt.id_tag = wtagrels.id_tag WHERE ( u.country = 'IE' OR u.country IN ( 'INT' ) ) AND CASE WHEN ( ( tt.id_tag = lptagrels.id_tag ) AND ( lp.id_lp > 0 ) ) THEN lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( ( ttt.id_tag = wtagrels.id_tag ) AND ( wc.id_wc > 0 ) ) THEN wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date > '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( od.id_od > 0 ) THEN od.id_author = td.id_tutor AND o.order_status = 'paid' AND CASE WHEN ( od.id_wc > 0 ) THEN od.can_attend_class = 1 ELSE 1 END ELSE 1 END AND ( t.tag LIKE "%Dictatorship%" OR t.tag LIKE "%democracy%" OR tt.tag LIKE "%Dictatorship%" OR tt.tag LIKE "%democracy%" OR ttt.tag LIKE "%Dictatorship%" OR ttt.tag LIKE "%democracy%" ) GROUP BY td.id_tutor HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 ORDER BY tutor_popularity DESC, u.surname ASC, u.name ASC LIMIT 0, 20 The problem The results returned by the above query are correct (AND logic working as per expectation), but the time taken by the query rises alarmingly for heavier data and for the current data I have it is like 10 seconds as against normal query timings of the order of 0.005 - 0.0002 seconds, which makes it totally unusable. Somebody suggested in my previous question to do the following:- create a temporary table and insert here all relevant data that might end up in the final result set run several updates on this table, joining the required tables one at a time instead of all of them at the same time finally perform a query on this temporary table to extract the end result All this was done in a stored procedure, the end result has passed unit tests, and is blazing fast. I have never worked with temporary tables till now. Only if I could get some hints, kind of schematic representations so that I can start with... Is there something faulty with the query? What can be the reason behind 10+ seconds of execution time? How tags work in this system? When a tutor registers, tags are entered and tag relations are created with respect to tutor's details like name, surname etc. When a Tutors create packs, again tags are entered and tag relations are created with respect to pack's details like pack name, description etc. tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All individual tags are stored in tags table. The explain query output:- Please see this screenshot - http://www.test.examvillage.com/Explain_query_improved.jpg

  Read the article

• mysql to excel exporting data using php getting html code also

  - by pmms

  hi all following is code for getting xlsheet from mysql using php ` if( ($_POST['Submit']=='generateexcel')) { $tblname=$_GET['generateexcel']; global $obj_mysql; $table="tbl_js_login"; // this is the tablename that you want to export to csv from mysql. function exportMysqlToCsv($table,$filename = 'export.csv') { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = '"'; $csv_escaped = "\"; $sql_query = "select fld_id, fld_fname,fld_lname from $table"; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; $out1 = strip_tags($out); } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out1)); // Output to browser with appropriate mime type, you choose ;) header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out1; exit; } exportMysqlToCsv($table); } include_once $path."includes/jobseeker_form.php"; /* function is_duplicate($login_name) { global $obj_mysql; $sql="SELECT * FROM tbl_admin_details WHERE fld_login ='$login_name'"; $num=$obj_mysql-get_num_rows($sql); if($num==0) return false; else return true; }*/ ?` the above code we are using for genrating the xlsheet along with xlsheet we are getting html at th top . following is the screen shot of xlsheet please provide some help how to remove the html code from xlsheet

  Read the article

• Script throwing unexpected operator when using mysqldump

  - by Astron

  A portion of a script I use to backup MySQL databases has stopped working correctly after upgrading a Debian box to 6.0 Squeeze. I have tested the backup code via CLI and it works fine. I believe it is in the selection of the databases before the backup occurs, possibly something to do with the $skipdb variable. If there is a better way to perform the function then I'm will to try something new. Any insight would be greatly appreciated. $ sudo ./script.sh [: 138: information_schema: unexpected operator [: 138: -1: unexpected operator [: 138: mysql: unexpected operator [: 138: -1: unexpected operator Using bash -x script here is one of the iterations: + for db in '$DBS' + skipdb=-1 + '[' test '!=' '' ']' + for i in '$IGGY' + '[' mysql == test ']' + : + '[' -1 == -1 ']' ++ /bin/date +%F + FILE=/backups/hostname.2011-03-20.mysql.mysql.tar.gz + '[' no = yes ']' + /usr/bin/mysqldump --single-transaction -u root -h localhost '-ppassword' mysql + /bin/tar -czvf /backups/hostname.2011-03-20.mysql.mysql.tar.gz mysql.sql mysql.sql + rm -f mysql.sql Here is the code. if [ $MYSQL_UP = "yes" ]; then echo "MySQL DUMP" >> /tmp/update.log echo "--------------------------------" >> /tmp/update.log DBS="$($MYSQL -u $MyUSER -h $MyHOST -p"$MyPASS" -Bse 'show databases')" for db in $DBS do skipdb=-1 if [ "$IGGY" != "" ] ; then for i in $IGGY do [ "$db" == "$i" ] && skipdb=1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$DEST$HOST.`$DATE +"%F"`.$db.mysql.tar.gz" if [ $ENCRYPT = "yes" ]; then $MYSQLDUMP -u $MyUSER -h $MyHOST -p"$MyPASS" $db > $db.sql && $TAR -czvf - $db.sql | $OPENSSL enc -aes-256-cbc -salt -out $FILE.enc -k $ENC_PASS && rm -f $db.sql else $MYSQLDUMP --single-transaction -u $MyUSER -h $MyHOST -p"$MyPASS" $db > $db.sql && $TAR -czvf $FILE $db.sql && rm -f $db.sql fi fi done fi

  Read the article

• posting multiple radio button values to mysql using "foreach"

  - by jeansymolanza

  i have adjusted my code slightly but i am still having difficulty posting it to the table. could someone please provide me with an example of the foreach array? form page <div style="padding: 15px;"> <span class="loginfail" style="font-size:24px; font-weight: bold">Notifications</span><p> <?php include("progress_insertcomment.php"); ?> <?php // Make a MySQL Connection mysql_select_db("speedycm_data") or die(mysql_error()); $query_comment = "select * from tbl_alert order by id desc limit 1"; $comment = mysql_query($query_comment, $speedycms) or die(mysql_error()); $row_comment = mysql_fetch_assoc($comment); $totalRows_comment = mysql_num_rows($comment); ?> <!--- add notification ---> <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <span id="sprytextarea1"> <textarea id='comment' name="comment" style="height: 75px; width:330px;"><?php echo $row_comment['comment']; ?></textarea> </span> <p> <button type="submit">Add</button> <input type="hidden" name="notc" value="1"/> </form> <!--- notification history ---> <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <table border="0" cellspacing="2" cellpadding="2"> <?php if ( $row_comment == 0 ) { echo "<span style='font-size: 11px;'>No current alerts.</span>"; } else { // Get all the data from the "example" table $result = mysql_query("SELECT * FROM tbl_alert ORDER BY id DESC") or die(mysql_error()); while($rows=mysql_fetch_array($result)){ ?> <tr> <td> <?php echo "<div class='bubble'><div class='pimped'> <blockquote>" . $rows['comment'] . " </blockquote></div> <cite><strong>" . $rows['user'] . "</strong> @ " . $rows['date'] . "</cite> <span style='font-size: 10px;'> <p> <a href='editalert.php?id=". $rows['id'] ."' class='form' >Edit</a>&nbsp;&#8226;&nbsp;<a href='deletealert.php?id=". $rows['id'] ."' class='form'>Delete</a> </span> </div> "; ?> </td> <td valign="top" align="center"><div style="padding-left: 30px;"><span style="font-size: 10px;">Completed?</span> <p class="field switch"> <!--- determine status of notification ---> <?php $status = $rows['status']; ?> <input type="radio" name="selstatus" value="no" <?php if($status == 'yes') {echo 'checked';} else {echo '';} ?>/> <input type="radio" name="selstatus" value="yes" <?php if($status == 'yes') {echo 'checked';} else {echo '';} ?>/> <input type="hidden" name="statusid" value="<?php echo $rows['id']; ?>"/> <label for="radio1" class="cb-enable <?php if($status == 'yes') {echo 'selected';} else {echo '';} ?>"><span>Yes</span></label> <label for="radio2" class="cb-disable <?php if($status == 'no') {echo 'selected';} else {echo '';} ?>"><span>No</span></label> </p> </div></td> </tr> <tr> <td></td> <?php } } ?> <td align="center"><div style="padding-left: 30px;"> <button type="submit">Update</button> <input type="hidden" name="notc2" value="1"/> </div></td> </tr> </table> </form> </div> </body> processing <?php // 6) update notifications if (array_key_exists('notc2',$_POST)) { echo "<p style='font-size: 12px;'>Thank you. The notifications have been updated successfully.<p>"; echo "<p><span style='font-size: 12px;'> <a onClick=\"history.go(-1)\" class='form'>Return</a></p> <p></span> "; exit; }; ?> after doing some research i learnt that the only way to insert multiple radio values into the MYSQL table is to use arrays. there was a similar question raised somewhere on this site: http://stackoverflow.com/questions/1656260/php-multiple-radio-buttons that recommended using a foreach loop. use a foreach loop <?php foreach ( $_POST as $key => $val ) echo "$key -> $val\n"; ?> $key will be the name of the selected option and $val, well, the value. how would this apply to my situation as i am struggling to find any help on the internet. i understand that i would use this on the processing page to pull out any radio values from the previous page and then loop the INSERT MYSQL code within the process until it was all done. any advice would be appreciated! GOD BLESS.

  Read the article

• changing a picker to table view OR having multiple table views on the same view

  - by Brodie4598

  Hello - My iPad app was rejected due to my use of a picker. The picker was used to control a table view. In my view, a picker was displaying a series of items and when one of those items was selected, it used that selection to populate a table with data. (hopefully that makes sense). Now I need to do this without the picker so I need to have the data that was in the picker be represented in a table view. My question, is how do I have multiple tableViews in the same view? I'm guessing I need to have some kind of if statement in the tableview delegate methods, but I'm not quite sure what the if statement needs to be. Or maybe i'm thinking of this the totally wrong way thanks

  Read the article

• How To Query Many-to-Many Table (one table's values becomes column headers)

  - by CRice

  Given this table structure, I want to flatten out the many-to-many relationships and make the values in the Name field of one table into column headers and the quantities from the same table into column values. The current idea which will work is to put the values into a Dictionary (hashtable) and represent this data in code but im wondering if there is a SQL way to do this. I am also using Linq-to-SQL for data access so a Linq-to-SQL solution would be ideal. [TableA] (int Id) [TableB] (int id, string Name) [TableAB] (int tableAId, int tableBId, int Quantity) fk: TableA.Id joins to TableAB.tableAId fk: TableB.Id joins to TableAB.tableBId Is there a way I can query the three tables and return one result for example: TableA [Id] 1 TableB [Id], [Name] 1, "Red" 2, "Green" 3, "Blue" TableAB [TableAId], [TableBId], [Quantity] 1 1 5 1 2 6 1 3 7 Query Result: [TableA.Id], [Red], [Green], [Blue] 1, 5, 6, 7

  Read the article

• Syncronizing indices of function pointer table to table contents

  - by Thomas Matthews

  In the embedded system I'm working on, we are using a table of function pointers to support proprietary Dynamic Libraries. We have a header file that uses named constants (#define) for the function pointer indices. These values are used in calculating the location in the table of the function's address. Example: *(export_table.c)* // Assume each function in the table has an associated declaration typedef void (*Function_Ptr)(void); Function_Ptr Export_Function_Table[] = { 0, Print, Read, Write, Process, }; Here is the header file: *export_table.h* #define ID_PRINT_FUNCTION 1 #define ID_READ_FUNCTION 2 #define ID_WRITE_FUNCTION 3 #define ID_PROCESS_FUNCTION 4 I'm looking for a scheme to define the named constants in terms of their location in the array so that when the order of the functions changes, the constants will also change. (Also, I would like the compiler or preprocessor to calculate the indices to avoid human mistakes like typeo's.)

  Read the article

< Previous Page | 138 139 140 141 142 143 144 145 146 147 148 149  | Next Page >