SELECT count(*) c FROM full_view WHERE verified > ( DATE (NOW()) - INTERVAL 30 DAY) If I run that query it takes a split second but if I switch the comparison operator around it takes eons. Now the first way the count = 0 and the second way the count = 120000, but if I just count the whole table that also takes microseconds. But there...
I have 3 tables t1,t2,t3 each having 35K records. select t1.col1,t2.col2,t3.col3 from table1 t1,table2 t2,table3 t3 where t1.col1 = t2.col1 and t1.col1 = 100 and t3.col3 = t2.col3 and t3.col4 = 101 and t1.col2 = 102; It takes more time to return the result (15 secs). I have proper indexes. What is the optimal way o...
I have the following code that should, when run, update a table of "victims" of Her Royal Majesty Penelope the Queen of Sheep (it's work for someone, honest), however every time the code is executed it adds all new rows all over again. I was pretty sure I had safeguarded against that, but I guess not. What am I doing wrong here? require...
I have a table in my database which has information about a bunch of user uploaded images. One of the fields is "slug". Say the user uploads an image with the filename "46156142.jpg" and inputs the slug as "smiley-face", I want to be able to point to the image by navigating to "http://domain.com/smiley-face". The actual location of the f...
I'm dabbling with pChart and would like to start with a simple line graph showing the growth in membership over time. Y-axis would be # of membersX-axis would be time For each time datapoint, I need a corresponding total members datapoint. My user table is structured as: [user_id] [join_date] The approach I came up with on the bu...
I have asked how to DBSM and would like to proceed knowing more about MySQL. When I log into their website, I dont know which one to try. There are Community Server, Cluster, Workbench and Connector. Please guide. Note: I am working on excel database (which I found out on this website that it is not a good way to do it) with size o...
I've been a'Googling and there's all sorts of various forms of crazy ideas out there, but, dear Stack Overflow geniuses, is there a stable, reliable form of converting a postgresql database to a mysql database on Linux? ...
I am having so much domain name in my DB, for example www.yahoo.com/duniya.html www.yahoo.com/hero.html www.123musiq.com/ www.123musiq.com/home.html www.123musiq.com/horo.html www.123musiq.com/yuva.html www.sensongs.com/ www.sensongs.com/hindi.html www.sensongs.com/yuva.html www.sensongs.com/duniya.html www.sensongs.pk/duniya1.html i ...
Is there a way I can make a query in MySQL that will give me the difference between two timestamps in seconds, or would I need to do that in PHP? And if so, how would I go about doing that? ...
Ok, I have to put null values last. The query should run under Oracle and MySQL. I've already came up with ORDER BY CASE WHEN some_table.ord IS NULL THEN 9999999999 ELSE some_table.ord END I should use value > max(some_table.ord) instead of 9999999999. I think subquery to determine this value is too ugly here. If this was C+...
For some reason my $count displays the number 1 first instead of 0 I really don't know what's going on I want count to start at 0 and display 0 first then 1, 2 and so on can someone help me correct this problem? PHP code. function make_list ($parent = 0, $count = 0, $depth = 0) { global $link; if($count == 0){ echo '<o...
Hello All, i got problem when i install mysql on my fedora11 machine This was the error face gem install mysql Building native extensions. This could take a while... ERROR: Error installing mysql: ERROR: Failed to build gem native extension. /usr/bin/ruby extconf.rb can't find header files for ruby. Gem files wil...
I have a page listing shows that a band has played, along with all the other bands that played at that show. So far, I've been hand-coding the page, but I plan to move everything over to a table so it's sortable by band/easier to maintain. As of now, the plan is to have one 'Artists' field, where a typical entry would be ex. 'Britney Sp...
Hi all, I have a mysql Database and 2 tables let's say clients and schools. Now each table has columns latitude and longitude. And I need do make a SELECT for example from second table where schools are in a given radius of one record from first table. Calculations should be made based on latitude and longitude. PS: I am using PHP. ...
I am having 3 tables user, songs and age table the user and songs having user_id each having 200k records suppose i need to make a range like selecting the records from age where age is more than 20 and from songs table the songs which is released after 2000 The virtual join table of mysql does nt work for this scenario as it taking m...
I have a database created with a GUI tool and I've noticed what appears to be an inconsistent use of KEY (aka INDEX) definitions: CREATE TABLE `foo_bar` ( `foo_id` int(10) unsigned NOT NULL, `bar_id` int(10) unsigned NOT NULL, PRIMARY KEY (`foo_id`, `bar_id`), KEY `foo_bar_fk2` (`bar_id`), -- <== ??? CONSTRAINT `foo_bar_fk1` F...
Hi, I have a database (MySql) and need to store some results from a web service monthly. The data can have 10 results today but may have 200 next month. I need to use a BI tool to create charts and what not. Someone proposed to serialize the data and save the blobs in the database, while the solution seems to work, I have a gut feel...
DELETE FROM Books INNER JOIN (Classes, Class_Books) ON (Books.ISBN = Class_Books.ISBN AND Class_Books.Class_ID = Classes.Class_ID AND Classes.Term_ID = 63) WHERE Year = '""' Gives Error: #1064 - You have an error in your SQL syntax; Replacing DELETE with SELECT it works fine though ...
I have a MySQL table and I am looking to create a stats page on the data. Id (integer), Pri (integer), blab1, blab2, blab3, ect... The "pri" field has a number between 0-1000. I would like to how the rows are spread across the pri in the table. I am looking for a way to group the "pri" by 100s and count the number of rows in each gro...
To extent my understanding on MySQL. 1) Is going for 64bit help? Do I go with installation or stand alone? 2) If I am going to use 64-bit in MySQL Community Service, will it affect the MySQL Workbench which only available in 32-bit? If yes, in what sense? 3) Does the size of the file affect the speed of MySQL when doing calculation, a...