I am currently working on a website which needs some optimisations ... since the front page takes about 15-20 seconds to be loaded I thought that some optimisation would be nice. Here is one query that appeared on the MySQL slow query log: SELECT a.user,a.id FROM `profil_perso` pp INNER JOIN `acces` a ON pp.parrain = a.id INNER JOIN `a...
Hey everyone, I have a string that is contained inside of a wordpress install (the name of a server) thousands of times, across multiple columns, records and tables. I'd like to update it with the location of another server - we are moving the content over. So the source would be something like http://my-server1/some/link/to/something...
How do you find out how many queries have been executed since the MySQL server has started? ...
Heres my problem. I have a mysql table called quotes. In one of the rows, a quote contains the folloqing characters ‘ and ’ Now the row collation is utf8__unicode__ci When using MySQL Query Browser and PHPMyAdmin to retrive the rows the quotes come out as intended. How ever when i retrive them from the database using PHP and display ...
Hi all. I want to create a current,30, 60, 90, day aging reports in Matrix Format using mysql query For example, ClientName Current 1-30 31-60 >90 Total AAA 3000 1500 4500 BBB ...
Hello, I'm experimenting with a tagging system which is a many-to-one relationship. My schema is: items table: item_id comment comment _ tags: item_id tag_id tags table: tag_id tag_name I've been reading of implementation designs at the links at the bottom but got stuck. I can insert tags without a problem. How do I fetch ...
I would like to produce a character list of all of the first letters of column in my database. The SQL below illistrats what I would like to return. SELECT DISTINCT first_character(name) FROM mydatabase Is there a way to do this in MySQL? EDIT What is the advantage of using SUBSTRING over LEFT and vice versa? EDIT Currently there a...
I'd like the stored procedure to return a list of data, just like what "select" do. But the PHP/MySql engine my hosting service does not support that feature. The only output parameter I can provide is primitive types, like int, varchar and so on. So I tried to combine the whole list into one big string with the help from cursor. But ...
I'm using PHPMyAdmin and I've got a MySQL table column called "timestamp." The type (surprise!) is TIMESTAMP, and in 'attributes' I've set it to ON UPDATE CURRENT_TIMESTAMP. However, each new record gets a timestamp that looks like this: 0000-00-00 00:00:00 I have explicitly set the default value to none, but when I save and come bac...
I am looking to design a database for a website where users will be able to gain points (reputation) for performing certain activities and am struggling with the database design. I am planning to keep records of the things a user does so they may have 25 points for an item they have submitted, 1 point each for 30 comments they have made...
I have a table whose primary key is used in several other tables and has several foreign keys to other tables. CREATE TABLE location ( locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ... ) ENGINE = InnoDB; CREATE TABLE assignment ( assignmentID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, locationID INT NOT NULL, FOREIGN...
I have a poll that has five 1-5 star ratings and I need to store the values individually for each user. Is there a method similar to bitfields where I can store these enumerated values (1, 2, 3, 4, or 5 for each rating) and easily extract them? At the moment my best bet would be to store a serialized PHP array, however that would take u...
Is it possible to check if a (MySQL) database exists after having made a connection. I know how to check if a table exists in a DB, but I need to check if the DB exists. If not I have to call another piece of code to create it and populate it. I know this all sounds somewhat inelegant - this is a quick and dirty app. ...
Hi, I'm repeatedly getting this in my server error log for MySQld. On my PHP (MySQLi) or SQL client (not sure what they use, maybe MySQLi as well) calling some (not all) stored procedures, this happens and I get the error message "Lost connection to MySQL server during query" (repeatedly). This procedure did actually work yesterday ;(. T...
Hello, My sql table is something like (message,created) I want to select those rows which are within X seconds from each other. Say the last message is within X seconds from NOW(), then it should select it. If the second last message is within X seconds from the last message then it should select it too. In other words each row should ...
I have two tables: CREATE TABLE table_a ( id SERIAL ); CREATE TABLE table_b ( id SERIAL ); I want to swap the tables out and set the auto_increment of the new table_a to the MAX(id)+1 of table_b. E.g. SELECT @A:=MAX(id) FROM table_a; SET @qry = CONCAT('ALTER TABLE table_b AUTO_INCREMENT =', @A+1); PREPARE stmt FROM @qry; EXEC...
I need to frequently duplicate one database on my MySQL server to a mirror database on the same MySQL server. I am writing a bash script that makes a mirror of my web application (PHP/MySQL) on a separate subdomain. Users can use this mirror to try out all kinds of crazy things without affecting the production environment. The script is...
Hi I am using hibernate and Mysql. I have a class with a boolean attribute called 'active'. The generated database table has the BIT data type. So far so good. I want to query this value but I don't know how to do it. I've tried SELECT * from table where active = 1 doesn't work, neither the following SELECT * from table where act...
How to do query and display the records until it reaches a certain number? Suppose you want to select student until the total sum of the student's money reaches 1000? Addition Student ID Student Name Student Money --------- ----------- -------------- 1 John 190 2 Jenny 290 ...
Money, Staff, Skill and preference to open source or commercial is neutral. Lets take the best of the best programmers (for arguments sake) and think about this: What will perform better overall: PHP & MySQL or ASP.Net & Microsoft SQL (I don't want biased answers, just looking for Performance, and Speed). ...