We are planning to develop a new website. Our goal is to load web pages quickly. What are all the techniques we need to follow. Can anyone give me good suggestions, forums links or articles. Our platform is PHP, MySQL, Javascript, and AJAX. ...
So, I need to find out how to do a fulltext boolean search on a MySQL database to return a record containg the term "C++". I have my SQL search string as: SELECT * FROM mytable WHERE MATCH (field1, field2, field3) AGAINST ("C++" IN BOOLEAN MODE) Although all of my fields contain the string C++, it is never returned in the search resu...
My Django project is going to be backed by a large database with several hundred thousand entries, and will need to support searching (I'll probably end up using djangosearch or a similar project.) Which database backend is best suited to my project and why? Can you recommend any good resources for further reading? ...
Hi All, I'm stuck trying to figure out a solution for the problem below. Its quite complicated. So stay with me. :-) I retrieve a field from the user table which has his friends' user ids in the form of CSV (3,4,5,6,7) There is another table called transaction which looks something like this tid user1 user2 type_of_trade 1 ...
I have two tables in my database (let's say t1 and t2) and I have a field of type BIT in each table (let's call it field1), what I want to do is have the same functionality obtained by using the && boolean operator (like in C, C#, Java .. etc), or in other words I want to do this: select t1.field1 && t2.field1 from t1, t2 where .. (if f...
I'm trying to optimize some of the database queries in my Rails app and I have several that have got me stumped. They are all using an IN in the WHERE clause and are all doing full table scans even though an appropriate index appears to be in place. For example: SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id...
I have a table containing the following: name type id : INT date1 : DATETIME date2 : DATETIME And I need to calculate the difference between date2 and date1. This is possible using the TIMEDIFF function in MySQL. However, is there a way to get the result of TIMEDIFF(date2,date1) using one select, and not using 2 helper selects...
I'm running PHP 5 and MySQL 5 on a dedicated server (Ubuntu Server 8.10) with full root access. I'm cleaning up some LAMP code I've inherited and I've a large number of SQL selects with this type of construct: SELECT ... FROM table WHERE LCASE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( strSomeField, ' ', '-'), ',', ''), '/', '-'), ...
Hi All, I have used PHP to connect to MYSQL. I have an include where I put database information, connection info, etc and I include it were necessary. What prevents someone from crawling my site, finding this include and connecting to my db? I do typically limit to few privs in MySQL so for example a user that just can insert/update d...
I have two tables that have the same structure; one contains permanaent data, and one is cleared and reset on a regular basis. I need to make the same select statement work on both as if they were just one table This is what I tried: SELECT * FROM a,b WHERE 1; Where a and b have the same structure; ...
Which is the better alternative? I've noticed PDO let's you return as an array or an object. I've always used arrays when using my old functions (mysql_fetch_assoc()) but I've just written a wrapper class for the PDO object and I was curious as to which is better. I suppose an object would be stricter... you can change/add to a returned...
I know how to map a list to a string: foostring = ",".join( map(str, list_of_ids) ) And I know that I can use the following to get that string into an IN clause: cursor.execute("DELETE FROM foo.bar WHERE baz IN ('%s')" % (foostring)) What I need is to accomplish the same thing SAFELY (avoiding SQL injection) using MySQLDB. In the a...
I have 3 tables. For the purposes of this example I will simplify it as much as I can. First table contains movie ids and names, second table contains genre ids and genre names (action, drama, etc). 3rd table stores the genre id associated with each movie, since some movies have more than one. It has 2 columns, genre_id and movie_id. F...
I'm using a query which generally executes in under a second, but sometimes takes between 10-40 seconds to finish. I'm actually not totally clear on how the subquery works, I just know that it works, in that it gives me 15 rows for each faverprofileid. I'm logging slow queries and it's telling me 5823244 rows were examined, which is odd...
Which is the valid syntax of this query in MySQL? SELECT * FROM courses WHERE (now() + 2 hours) > start_time *note: start_time is a field of courses table* ...
I've read that although SQL is meant to be standardised, it is implemented different etc with different databases. I have only ever used MySQL for databases. What I would like to know is what other databases share the same syntax? I am using PDO now, and I would like to set a flag to allow the user to specify which database they would l...
hi, I have set my database fields "username" and "email" to unquie, when using the code below this only works if the "username" already exists, an error is then echoed. If they email exists the user gets a mysql duplicate error, when the same error as above should be shown. <?php require_once ( 'connection.php' ); $username=$_POST['us...
We've been doing some testing with Coldfusion and MySQL and are a bit puzzled. We know that there are different 'sql-mode' flags you can set in the /etc/my.cnf file which will cause MySQL to behave certain ways, depending on the modes defined. We know you can also set these flags whenever starting MySQL using '--sql-mode=' flags. In /et...
After much work I finally got a rather complicated query to work very smootly and return results very quickly. It was running well on both dev and testing, but now testing has slowed considerably. The explain query which takes 0.06 second on dev and was about the same in testing is now 7 seconds in testing. The explains are slightly d...
I want to compute a checksum of all of the values of a column in aggregate. In other words, I want to do some equivalent of md5(group_concat(some_column)) The problem with this approach is: It's inefficient. It has to concat all of the values of the column as a string in some temporary storage before passing it to the md5 function...