Rtyjn

MySql - Abysmal Performance I am trying to run a relatively simple query on a table that has half a million rows. It's just a small fragment I'm using to test the values I get back are correct. The problem is this query takes over 20 minutes to complete, which seems unusually slow even for 500,000 records. DROP VIEW IF EXISTS view_temp_sortie_stats; CREATE VIEW view_temp_sortie_stats AS SELECT server_id, session_id, ucid, role, sortie_id, ( SELECT COUNT(sortie_id) FROM raw_gameevents_log WHERE sortie_id = l.sortie_id AND server_id = l.server_id AND session_id = l.session_id AND target_player_ucid = l.ucid AND event = "HIT" ) AS HitsReceived FROM raw_gameevents_log l WHERE ucid IS NOT NULL AND sortie_id IS NOT NULL GROUP BY server_id, session_id, ucid, role, sortie_id; SELECT * FROM view_temp_sortie_stats; Here is my table: Next I tried to add indexes for server_id, session_id, sortie_id to see if it would improve - this took more than 1...

syntax error at 'SELECT LAST_INSERT_ID()' If I run my query in python, it returns a syntax error. However, if I run this with navicat, it work's fine. Why doesn't it run in python? sql_query INSERT INTO solarsystem ( solarsystemName, solarsystemPositionX, solarsystemPositionY, solarsystemSectorId, solarsystemAngle ) VALUES ('Lima [698/562]',698,562,13,171); SELECT LAST_INSERT_ID(); error 1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID()' [EDIT] create.py [...] args = (solarsytem['solarsystemName'], solarsytem['x_pos'], solarsytem['y_pos'], solarsytem['sector_id'], solarsytem['angle']) with self.engine.begin() as connection: result_proxy = connection.execute(sql_query, args).first() [...] UPDATE If I try the INSERT as single que...

Select subset for each item I'm using SQL (from a PHP script) to get a list of items from my database, i.e. SELECT `id`, `title` FROM `books` WHERE `removed` = NULL; I also have a table containing articles, with a column bookId which refers to the id of the book. bookId SELECT `id`, `title` FROM `articles` WHERE `bookId`=1; Now, I want to prepare a list of all books, and for each book I want to see all related articles (e.g. just the title). So far, the only way I could image to get such a list is to first perform a query to get all the books, and then for each book to select the related articles. However, this would require a lot of separate SQL calls. Does anyone have any idea wether it would be possible to get such a list with a single call? did you check SQL Joins ? use this url w3schools.com/sql/sql_join.asp let me know if it is what you are looking for – Mhmd Jun 30 at 9:41 ...

Update query to order position records table i have table Z like: id | data | order | ----+--------+-------- 31 | a | 0 | 32 | b | 1 | 33 | c | 2 | and i use table-dragger.min.js to set position row to update order. how create update query to update order with ajax table-dragger.min.js var el = document.getElementById('table'); var dragger = tableDragger(el, { mode: 'row', dragHandler: '.move', onlyBody: true, animation: 300 }); dragger.on('drop', function() { arr = ; tbl = $('table tbody > tr'); tbl.each(function(index) { arr[index] = this.id; }); $('textarea').text(arr) }); table { border-collapse: collapse; } table, th, td { border: 1px solid black; text-align: center; } th:nth-child(1) { width: 50px; } th:nth-child(2) { width: 200px; } .move:hover { cursor: pointer } textarea { width: 300px } <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1....

Overlapping issue in dates and rates in MySQL and PHP Issue: overlapping rates normal date: 2018-06-30 to 2018-07-06 normal rates: 4500 for 2018-06-30 to 2018-07-06 overlapping dates: 2018-07-03 to 2018-06-06 overlapping rate: 1500 for 2018-07-03 to 2018-06-06 Output I want : (2018-06-30 to 2018-07-06)** 6/30 7/01 7/02 7/03 7/04 7/05 ----------------------------------------- 4500 |4500 |4500| 1500| 1500| 1500 but getting 6/30 7/01 7/02 7/03 7/04 7/05 ----------------------------------------- 4500 |4500 |4500 | 1500 | 1500 | 1500 |4500 |4500 | 4500 Here error like displaying both rates overlapping and normal rates also Actual output what I want display Query: SELECT r.id, r.title, d.roomTypeId, SUM(IFNULL( t.basePrice , 0)* 1) AS basePrice, SUM(IFNULL( IF(d.overLap = 1,d.baseCharge,IF(d.isDayRate = 1,d.baseCharge,d.baseCharge)),0) * 1) AS priceAftDisc, GROUP_CONCAT((IFNULL( IF(d.overLap = 1, IF((1 - t.baseOccupancy)...

MySQL special characters effect search result [on hold] I have a record like this: 214/13rr3323 i use this query ... WHERE c.name LIKE ? ... $etc->execute(array("%".$this->word."%"); stops searching after "/" character. Why does this happen? I want to search like this 21413rr3323 but without "/" character, I got no results. thanks anyway i need a sql query that NOT ALLOWS characters except /[^a-zA-Z0-9]/ SELECT product.size FROM product.size WHERE replace(product.size, '/', '') LIKE ? How can i do multiple replace ? Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question. I don't quite understand. I wouldn't expect...

How can i select data stored in a database with PHP the problem i am facing is i want to read a value that is auto incemented and used, my database takes the following design: mysql_query("INSERT INTO category (category,image) VALUES ('$name','$default_item')"); $image_name = '$id' . '.jpg' move_uploaded_file($_FILES["image"]["tmp_name"], "../images/category/" . $image_name); Why dont you just auto increment image as well? You dont need it to be a string – Arex Jun 30 at 8:39 I have been able to find the solution of what i needed on my own, Thanks anyway – Malek Salameh Jun 30 at 20:06 ...

What kind of server can serve 1000-1500 visitors per second from pop ads? I have a website that works on NGINX + APACHE Uses perl script & MySQL database Intel Xeon E5-1630v3 with 32GB RAM and SSD Lots of pages/visitors and tons of processes per second But I have only one case Whenever I tried to buy visitors through popunder/popups Once users reach 300 or more per second CPU usage is rising CRAZY I think this server is unable to handle this size of visitors Any suggestions? Thanks. By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

How to input the echo value to complete the query? [on hold] Help pls. I just want to complete the query but I don't know how to put the echo value show tables from (echo value which is the database) . show tables from (echo value which is the database) <?php if (isset($_POST['database'])){ echo $_POST['database']; $qry = "show tables from "; $res = mysqli_query($link,$qry); while ($row = mysqli_fetch_array($res)){ echo $row[0]."<br>"; } } Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question. When you echo $row inside the while loop, you use the column name as the array index, not a number, eg $ro...

i want to load data from mysql to jsp but I have an unprocessable error enter code here this is java code to load data from database to jsp package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import model.Category; import utils.DBConnect; public class CategoryDAO { public ArrayList<Category> getListCategory() throws SQLException { Connection connect = DBConnect.getConnection(); ArrayList<Category> listCategory = new ArrayList<>(); String sql = "SELECT * FROM category"; PreparedStatement ps = connect.prepareCall(sql); ResultSet rs = ps.executeQuery(); while (rs.next()) { Category category = new Category(); category.setCategoryID(rs.getInt("CategoryID")); category.setCategoryName(rs.getString("CategoryName")); listCategory.add(category...

SQL with between in 2 columns statment my data : id a b price 1 2.00 1.00 2.50 2 2.00 2.00 3.00 3 3.00 1.00 4.50 4 3.00 2.00 2.50 5 3.00 3.00 3.00 6 4.00 1.00 4.50 my question is how get data in id 2 to 4 with sql "between" from columns a and b not columns id 2 2.00 2.00 3.00 3 3.00 1.00 4.50 4 3.00 2.00 2.50 my code sql (but not work): sql = "SELECT * FROM data_table WHERE (( BETWEEN (a = 2.00, b = 2.00 ) AND ( a = 3.00, b = 2.00 ))"; 1 Answer 1 Not sure why you making the query complex. Try this Select * from data_table where id between 2 and 4 not between id but from columns a and b – iDea Brains 11 mins ago do u wan...

Error during installation - Database initialization failed During the final step of the installation of MySQLServer (Apply Configurations), I get a strange error. I can't get it. Here's the Log : Attempting to add a Windows Firewall rule with command: netsh.exe advfirewall firewall add rule name="Port 3306" protocol=TCP localport=3306 dir=in action=allow Ok. Successfully added the Windows Firewall rule. Ended configuration step: Updating Windows Firewall rules Beginning configuration step: Adjusting Windows service Attempting to grant Network Service require filesystem permissions. Granted permissions. Adding new service New service added Ended configuration step: Adjusting Windows service Beginning configuration step: Initializing Database Attempting to run MySQL Server with --initialize-insecure option... Starting process for MySQL Server 8.0.11... Starting process with command: C:Program FilesMySQLMySQL Server 8.0binmysqld.exe --defaults-file="C:ProgramDataMyS...

Join 3 tables with Count I am trying to write a simple query that will display all projects and their total number of team members, sorted alphabetically by project. If a project does not have assigned team members, that project should still be included in the output. CREATE TABLE Project ( ID INT IDENTITY(1,1), ProjectName VARCHAR(50), DueDate DATE) CREATE TABLE Employee ( ID INT IDENTITY(1,1), EmployeeName VARCHAR(50) ) CREATE TABLE ProjectAssignment ( ID INT IDENTITY(1,1), ProjectID INT, EmployeeID INT) INSERT INTO Project VALUES ('Alpha', '1/1/2040'), ('Bravo', '3/1/2030'), ('Charlie', '2/1/2017'), ('Delta', '4/1/2017') INSERT INTO Employee VALUES ('John'), ('Beth'), ('Tom'), ('Kim'), ('Jack') INSERT INTO ProjectAssignment VALUES (1, 1), (1, 2), (2, 2), (2, 3), (3, 3), (3, 4), (1, 3) --TABLE Project: ID ProjectName DueDate 1 Alpha 2040-01-01 2 Bravo 2030-03-01 3 Char...

How to display @Lob image from mysql database using spring mvc and jsp There is link to github: https://github.com/Lukszn/ProjectProfile I'm using Spring 4.3.7.RELEASE, MySQL Connector Java: 5.1.39 and hibrnate: 5.2.9. Finaly There i have User and his Account model. In account i have @Lob accPicture and some Strings(+ get/set). I'm trying a lot of answers from stackoverflow and documentation to show Account image, but without success. Last think what I do : created own ImageController. I successfully stored image in database but when I'm trying to display it in my jsp, it is showing "HTTP Status 400 - The request sent by the client was syntactically incorrect." Firstly I show you my User model: @Entity @Table(name = "users") public class User implements Serializable{ /** * */ private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private long id; @Column(unique = true) ...

How do I add search functionality to my website? [on hold] I am not a developer and I do not know much about web development but I am a beginner Pascal(Delphi) and C# programmer. I am currently using Adobe Muse to develop my website. I am close to completion but I would love to add search functionality to my site. I Do not want to use Google custom search and I know that I will have to learn some code and I am willing to do so. If someone could just point me in the right direction.I would love to do the same as this website, when Samsung is searched it provides three columns with pictures and headings of the web pages I uploaded the website I'm working on to a sub domain, ultimate1856.esy.es Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, pleas...