Hi Guys! I want to create a 'google analytics' type application for the web - i.e. a web-based tool to do some reporting and graphing for my database. The problem is that the database is HUGE, so I can't do the queries in real time because they will take too long and the tool will be unresponsive. How can I use a cron job to help me...
I have a table with user_id and lap_time and I'm running the following query: SELECT `Lap`.`id`, `Lap`.`user_id`, `Lap`.`lap_time` FROM `laps` AS `Lap` WHERE `Lap`.`lap_time` < 57370 GROUP BY `Lap`.`user_id` ORDER BY `Lap`.`lap_time` ASC I am trying to get the all the laps that are quicker than X but only unique users. The query ...
Given the query: SELECT DISTINCT score.* FROM score LEFT OUTER JOIN tag ON (tag.id_score = score.id) INNER JOIN score AS score_1 USING (id) WHERE (score.song_name LIKE '%[Upload]%' OR score.artist_name LIKE '%[Upload]%' OR score.creator_name LIKE '%[Upload]%' OR tag.name LIKE '%[Upload]%') AND (score_1.song_name LIKE '%[Che...
I am trying to decide if I need to use a view for a particular set of tables. I was wondering if when deleting a row from a view, will it delete the appropriate rows from the base tables that the view was created upon? I am using mysql. ...
What is difference between NULL and Empty String in Mysql ? How much storage space it will take ? For Example . In the user table name : NULL - How much space its take phone : -How much space its take ...
Various incarnations of this question have been asked here before, but I thought I'd give it another shot. I had a terrible database layout. A single entity (widget) was split into two tables: CREATE TABLE widgets (widget_id int(10) NOT NULL auto_increment) CREATE TABLE widget_data ( widget_id int(10), field ENUM('name','size','color...
Normally what happens is that requests seem to back up, and apache begins to consume more and more memory and CPU, until eventually it runs out of RAM and then ALL apache processes die, leaving all visitors with no website. This seems to be a common problem from people I speak to but I can't seem to find a solution! Any help much appre...
So I'm having serious speed problems using a left join to count ticket comments from another table. I've tried using a sub-select in the count field and had precisely the same performance. With the count, the query takes about 1 second on maybe 30 tickets, and 5 seconds for 19000 tickets (I have both a production and a development serve...
Hi guys, I've read some posts about this issue but none cover this issue. I guess its not possible, but i'll ask anyway. I have a table with more then 50.000 registers. It's an old table where various insert/delete operations have taken place. That said, there are various 'holes' some of about 300 registers. I.e.: ..., 1340, 1341, 16...
I want to know if the mysqlplus gem is a better database driver than the common Ruby mysql gem? I used to have some problems in my Rails application, like: ActiveRecord::StatementInvalid: Mysql::Error: MySQL server has gone away ...
Is it possible to group by more than one column e.g GROUP BY fV.tier_id AND 'f.form_template_id' in a mySQL statement? ...
I'm try to use mysqldump to export only the DB schema -- no data, no additional SQL comments, just the CREATE TABLE commands. Here's what I've got so far: mysqldump -h localhost -u root -p --no-data --compact some_db It almost achieves what I want, but I'd like to eliminate the "character set" lines (those like the first 3 lines in t...
I'm working with another dev and together we're building out a MySQL database. We've each got our own local instances of MySQL 5.1 on our dev machines. We've not yet been able to identify a way for us to be able to make a local schema change (eg: add a field and some values for that field) and then export some kind of script or diff file...
I have a simple database and want to update an int value. I initially do a query and get back a ResultSet (sql::ResultSet). For each of the entries in the result set I want to modify a value that is in one particular column of a table, then write it back out to the database/update that entry in that row. It is not clear to me based o...
I am using JNDI with Tomcat6 to manage Mysql connections, my Catalina/domain.com/ROOT.xml has: <Resource name="jdbc/db" auth="Container" type="javax.sql.DataSource" username="db1" password="somepass" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/db?autoReconnect=true" maxActive="15" maxIdle=...
specialcharacters åöö in the database cant be shown when i retrieve the rows and echo it. what can i do to have them shown propertly? ...
im coding a guestbook and i want new lines and spaces to be shown when i show the posts? when i post a thread now everything is shown on one line. help! ...
Suppose that I have a database table that contains information on cities across the United States. My display shows information first grouped by State, then County, and finally by City something like below: Alabama Autauga County city 1 city 2 etc Baldwin County city 1 city 2 etc County etc Alaska Alaska county 1 city 1 alaska cou...
I believe there is a function that I can use to determine the string length but I can't find it on Google. What I want is all rows greater than 255 characters. Can someone please shed some light? ...
I have a column in mysql of float type, I need to load currency like numbers($300) into this column, how do I do that in MYSQL? (let's not argue about the choice of float for this column) ...