MYSQL :: Which Is Faster In MySQL? Comparing Unix Times, Or DATETIMES?

Compare Datetimes
How do you compare two datetimes to find out which is earlier or later? I've tried the following (log.time_added is a datetime):Code:

...where log.time_added >= FROM_UNIXTIME( 1163656800 )
AND log.time_added <= FROM_UNIXTIME( 1163743200 )

and it returns all results from any date, including dates not in the specified range. I have confirmed that FROM_UNIXTIME is working correctly, it just isn't comparing the dates correctly.

Faster Transfer - MySQL
i have my database, but i have to connect to other database (in anoter place) and get the data from that database to mine... the problem is there are too many data (500.000).... How can i do the transfer faster.

Mysql Result Times Over 15 Seconds
I am having problems with result times. I run a search from my website and it takes around 15 seconds to generate the mysql query and then 40 seconds to load the page.

MySQL Via Unix Sockets
I'm using java+JDBC connector. Where do I need to set up something to work with /var/lib/mysql/mysql.sock

Two Versions Of Mysql On Same Unix Box
I have mysql 3.23.58 version running on my unix box. I installed a new application on it which need mysql version > 4.0 so I thought of having two mysql versions running on the same unix box. I have first installation in /usr/local/mysql and I untared the new version in /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23.

I am following instructions given in http://dev.mysql.com/doc/refman/4.1/en/multiple-servers.html I also created a empty file and named it as mysql.sock.new and stored it in /var/local. When I try to start this new version from /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23 using ./bin/mysqld_safe --datadir=/var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23/data & it says "A mysqld process already exists". Please tell me where I am going wrong and how to configure it properly?

Mysql Unix Client
I there, I would like to know if there is a mysql client that we can use from unix to connect to a mysql windows server?

MySQL Query Times Out From Page But Not Console...
The query works ok when executed from the console, but never executes from the any page. - When I use MySQL Administrator to view the threads this particular thread gets stuck on "copying to tmp table on disk"...

Can't Insert A Unix Timestamp Into Mysql
Just upgraded to 4.1.8 and when I create a field with a date value (field is trdate), it always has default of 0000-00-00. And when I insert values with unix timestamp values (which I prefer, and they come from php's strtotime function),the record does not insert properly. I tried to set, through phpmyadmin, unix/current timestamp and to no avail.

If I use a strtotime php function to convert a date into a timestamp, what is the proven way to make sure that value is saved during inserts? I am on the mysql manual, searched this board, but nothing works.

Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options.

Here is what I did. But I dont see it working yet.

1. cd /var/db/mysql
2. chown mysql slowquery.log
3. touch /usr/local/etc/my.cnf
4. chown mysql /usr/local/etc/my.cnf

vi my.cnf

[mysqld_safe]
-u mysql
--log-slow-queries=/var/db/mysql/slowquery.log

so now when I type:

mysqladmin shutdown
and than

mysqld_safe &
my sql restarts but the log files are not being used.
also - how do I know if my my.cnf is being used at all?

Needed Help With Uninstalling MySQL 5 On Mac/Unix
I have MySQL 5 on my Mac 10.4 and I want to completely uninstall it because I think I have MySQL 4 running on it too. I'm not too sure how to check for this. I'm not the best with administrating a database.

Calling MySQL In Unix In One Line
I'm running MySQL in Unix and I need to run mysql without ever actually going into mysql. Basically I need to open the database, do what i need to do to the database and exit all in one line of code.

If I have to, I could save the sql in a file and go
mysql -h localhost -u ______ -p _________ << mysqlcode.txt
or something with Unix redirection/piping.

However, I'd like to do something more like
mysql -h localhost -u _________ -p ________ - (use tempdatabase; select * from test;)

Is there anyway to do this. The -e paramater looks like it might do that, but I can't figure out how to use it.

MySQL Generate UNIX Timestamp
Cam MySQL create a UNIX timestamp during an INSERT? I want to create a timestamp that is the number of seconds since January 1st 1970. Are there any inbuilt functions for doing so?

Invoking A MySql Stored Procedure From C In Unix
I've this problem where I have to invoke a MySql stored procedure from my C program. I searched a lot for this. But I couldn't find any solution to this problem.

How Do I Change Which Editor The Mysql Unix Client Uses?
Is it possible to tell the mysql client which editor to use when a user types "edit" at the mysql command prompt? I looked in the mysql client manuals online, but there was nothing about setting editor pref's.

Comparing Date In Mysql
I am a new member to this forum.
I am developing a form using PHP - mysql in which i need to compare date with the date field in the datebase . the type is "date" in the format 'yyyy-mm-dd'.
can we use the variable which has a string of type 'yyyy-mm-dd' in the query itself by using > or < operators , i have tried but i m not getting the answer .
i m using two forms one in html to enter the dates by the user and other in PHP for querying the databse.

Comparing Tables With Mysql
I have 2 tables with the same format (same columns). Table 'a' is a subset of table' b'. I.e. in an ideal situation all entries in table 'a' should exist in table 'b'.
I can uniquely identify an entry using 3 fields (no SQL key is setup)

I need to write an SQL query (or a few queries) that will compare the 2 tables and output:
Records that are in A but not in B
Records that exists on both (i.e. key match) but are different (any other value but the key is different).

Search MYSQL Date-range With Unix Timestamp?
I want to search for records that are ON or After a certain date in a column that uses MYSQL date form (yyyy-mm-dd) with a unix timestamp. Can this be done?

Search MYSQL Date Column With Unix Timestamp?
I want to search for records that are ON or After a certain date in a column that uses MYSQL date form (yyyy-mm-dd) with a unix timestamp.

Which One Is Faster?
is there any difference in speed between next two types of mysql queries?
1. insert into myTable set...
2. or insert into myTable (fields) values (values)
point is that i have to insert a lot of rows into one table, and i wonder which sql to use.

What Would Be Faster
// OPTION 1:

SELECT id, title, type, source
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1
AND id >= (SELECT FLOOR(MAX(id) * RAND())
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1)
ORDER BY id
LIMIT 1

// OPTION 2:

SELECT id, title, type, source
FROM ads_banners
WHERE live = 1
AND ads_zones_id = 1
ORDER BY RAND()
LIMIT 1

Faster To Use
is it faster to use the field names of a table or * to select all the fields. I know its not good to use * if you dont need all the fields but does it make any difference if you need them all?

Faster To Use C DBI
How does C,PHP and Perl DBI compare in speed with MySQL?

Import Dump From Unix Mysql To Windows Mysql
I am trying to load a dump from a MySQL database on my ISP's server to my development environment on a Win 2K machine (running MySQL). When I run the mysql command to run the load script, MySQL gives the following complaint:

"ERROR 1064 at line 21: You have an error in your SQL syntax. Check the manual t
hat corresponds to your MySQL server version for the right syntax to use near 'D
EFAULT CHARSET=latin1' at line 7"

The dump from the ISP's server is created using mysqldump. The ISP is running MySQL 4.1.20. My development environment is MySQL 4.0.26. I have no problem loading dumps made from my development environment usng phpmyadmin.

Linux Mysql Nfs : Bind On Unix Socket: Function Not Implemented
I have installed Mysql on red hat ES release 3 and i was working great.

For my applications, I decided to to put the data directory on another machine. The other machine is a big disk of storage (Adaptec snap server 1 terabyte)
It working great for all applications except Mysql

In fact, i put /var/lib/mysql on another machine and I made a nfs mount

When i try to start mysql i have this :

/etc/rc.d/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]

in the error message i have this

051028 11:12:33 mysqld started
051028 11:12:33 Can't start server : Bind on unix socket: Function not implemented
051028 11:12:33 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
051028 11:12:33 Aborting

051028 11:12:33 /usr/libexec/mysqld: Shutdown Complete

051028 11:12:33 mysqld ended

I have verified the permission and everything seems to be ok....

Achieving Faster
mysql 4.0.22 (win32)
Can anyone recommend best practices for the fastest way to remove large
numbers of rows at once?
I am diving my deletes into chunks (1000 rows at a time) but this still is
very slow.

Faster Way To Calculate
I'm looking for a faster way to calculate a 5-day moving average in a MySQL table. I have a database of stock prices for various different ticker symbols. I have a column that I use to store the 5-day moving average calculation, which is just the average stock price over the last 5 days.

What I am presently doing is querying the entire table, and sorting by ticker (ascending), then date (ascending). Then I am simply obtaining a row of data at a time and writing the calculated moving average back into the table for that particular row. Since each row has a unique row number associated with it, I can easily write a particular value to any specific row within the table.

Faster RAND() Query
I need to select from my db table ONE (1) random row, just one. So, I am doing this:

SELECT categories.cat_name, subcategories.sub_name, map.faq
FROM categories, subcategories, map
WHERE categories.cat_id=map.cat AND subcategories.sub_id=map.sub
ORDER BY RAND() LIMIT 1"

categories=10 rows
subcategories=20 rows
map=100 rows

Deleting Rows Faster
I am creating a temporary table which is simply a copy of two tables joined together, excluding rows which meet a certain criteria. For some reason, it is much faster for me to make a full copy of the joined tables, and then delete from the table the rows which meet the criteria, than it is to filter the rows in the first place.

CODEdrop table if exists active_customers;
create temporary table active_customers
     (select *
     from customers as c, customer_status as s
     where c.customer_ID = s.customer_ID);

delete from active_customers
where activity_status = 'I';

LIKE '%%' Always Faster Than FULLTEXT Search
Is LIKE '%%' always faster than FULLTEXT search on a 1GB (500k entries) text field index?

Creat Mysql In Unix And Pull/post Data From A WindowsXP Machine With A UserDSN?
Can I creat mysql in Unix and pull/post data from a windowsXP machine with a UserDSN?

Is there an Administrative Tool (User DSN) that I can utilize to pull/post data to a Unix account without having to through the long process of "FTP" and "XTerm windows" ?

LEFT OUTER Is Faster Than INNER Joins?
Maybe someone can explain this to me real quick. I have two identical queries, except that the join type changed. One uses inner joins:

SELECT OrderItems.product_id AS product_id
, OrderItems.code AS product_code
FROM Orders
INNER
JOIN OrderItems
ON Orders.id = OrderItems.order_id
INNER
JOIN Products
ON OrderItems.product_id = Products.id
WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR))
AND Products.active = 1
GROUP
BY OrderItems.product_id
and the other uses the basic LEFT OUTER JOIN:

SELECT OrderItems.product_id AS product_id
, OrderItems.code AS product_code
FROM Orders
LEFT OUTER
JOIN OrderItems
ON Orders.id = OrderItems.order_id
LEFT OUTER
JOIN Products
ON OrderItems.product_id = Products.id
WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR))
AND Products.active = 1
GROUP
BY OrderItems.product_id
When I run the two queries on the same data (using phpmyadmin), the left outer joins only take 0.12-0.13 seconds whereas the inner joins take a whopping 2.5+ seconds. I kinda thought that an inner join would be faster. I'm still bad with determining join types.... Is there a fundamental reasoning for this that I am missing?

How Can I Make SELECT Statement Even Faster?
Here is a SELECT statement I had a sql programmer help me create that worked and resulted in less than a second whereas it was taking 5-6 seconds for everybody (and growing as database got larger) before when I was using multiple select statements to achieve the same thing.

Now that I have this statement in production it works instantly or less than a second for anyone with less than 100 records to process; however I'm noticing certain users (card numbers) that this query applies to is taking up to 9 seconds for them and is growing longer the more records they accumulate when they start going over 100 records.

I don't understand why it's getting dramatically slower after this point. If they have thousands of records to process, is this query gonna start taking minutes? that'll be way too long. This is running on top-of-the-line servers (3+ghz 1GB RAM / SCSI drives). Code:

Query Runs Much Faster In MS Access Via ODBC
I have a query that takes over a minute to run with the mysql client. The
same query takes seconds in MS Access via ODBC.

I have no clue what is going on. Can someone kindly shed some light on
this.

I am using:

mysql Ver 12.18 Distrib 4.0.12, for Win95/Win98 (i32)
mysqld-nt Ver 4.0.12-nt for NT on i32

Which Is Faster? BINARY Or LOWER/UPPER Conversion
Which is faster?

A. Declare a string column col1 as BINARY indexed and perform "case insentive" lookups with SELECT * FROM table WHERE LOWER(col1) = LOWER(queried_word).

B. Declare a string column col1 as indexed and perform case insensitive lookups with SELECT * FROM table WHERE col1 = queried_word.

Select Query On Latin1 Or Utf8 Column: Which Is Faster?
Assume you have two varchar (or Text) columns named L and U which are
identical except that the charset for L is latin1 and the charset for
U is utf8. All the records in L and U are identical in terms of
content, consisting of only 7 bit ASCII characters. Both columns have
indexes of the same type (e.g. assume Unique indexes if you want).

Here's my question: Will the fact that column U has a utf8 charset
make select queries run slower on that column? For example, will the
query

Select * from table where U='blahblah'

run slower than the query

Select * from table where L='blahblah'

??

Significantly slower?

I'm thinking that a query on the latin1 column would go faster since
the program knows upfront that one byte equals one character, and
vice-versa; whereas in the same query on a utf8 column the program has
a lot more "overhead" because it has to constantly be determining how
many bytes represent a character. Since queries on string columns are
case insensitive, the program can't just do a byte-for-byte
comparision; rather, it has to compare *characters*, and sometimes
convert a character from upper to lower case, or vice versa, in order
to do the case-insensitive comparison.

The actual column in question is going to store URLs, so it should
only need to hold 7 bit ascii characters (in theory at least). So, in
terms of content, it shouldn't matter whether I make the column latin1
or utf8. But in terms of query speed....on, say, a few million
records...??

I would like to do everything in utf8 (web pages, forms, mysql
database columns, etc.). But since that one column might be heavily
queried, maybe I should make an exception and do it in latin1?? I wish
the mysql docs would speak to these issues....

Upload MySQL Database To A Unix Server - What Path To Upload To?
I am going to upload a MySQL database to a Unix server. However, what path do I upload to?
Something makes me thing /var?

Appear 2 Times
i'm having this problem finding out the right sql query.
i have two tables A & B
the userid in table A appears as FK in table B
i need to list userid that appear 2 times or less in table B which has a where clause column3='Yes'

how do i go about it>?

Connect From Unix Mysql Server To Ms Access Server
Im wondering is it possible to connect through my mysql db (running on fedora core 4) and access the ms access tables on the ms access server?

I have a connection setup the other way around using an odbc bridge, is it possible to do this the other way around enabling me to view / edit the access tables through mysql.

Dates And Times
I have a field in a DB called "customerJobDate" which is configured to store full dates (ODBC dates) e.g. "2006-03-06 08:30:00". Now this does work i can update insert and delete the records perfectly, But if i query the DB asking for all dates that match 2006-03-06 regardless of the time (Date only) then the result returns 0 rows unless any of the dates in the column have 00:00:00 as the time.

This works: 2006-03-06 00:00:00
This does not work 2006-03-06 08:30:00

I hope someone can help me as I am finishing this app and this is the only thing holding me back.

One To Many Multiple Times
I have a 'links directory' database that currently contains 4 tables: category, subcategory, region and links. My links table contains company names and, among others, three fields catID, subcatID and regID. Each of these are related to primary keys in the other three tables, category (catID), subcategory (subcatID) and region (regID).

My issue is that the records in the links table can be in multiple regions, multiple subcategories and multiple categories, but the way I have it designed, each record in links can only be associated with a single catID, subcatID and regID.

Currently the only workaround I can think of it to have duplicate entries in the links table for a single company, but with every variation of catID, subcatID and regID. This will result in a very large table, but I cannot currently think of any better idea.

Upload Times
I need to upload 2 star catalogues to the mysql database. the first time i tried this i filled up the HD in 2 Days, my instructor mentioned that journalling was taking up most of the space and decided to remove-it.

now, with journalling turned off, i have started re-uploading the catalogues, but have noticed a significant decrease in "write" speed.

my question is, does journalling speed up writing data into mysql tables? i have roughly 180GB of data to upload, and it took me 4 days to upload 3GB's when last time it took me this time to upload 100GB.

Dates And Times.
You can't have two timestamp columns in a database both with the default now().
- why I would want to is not the issue.

You can't use the data and time functions, such as CURRDATE()/CURRTIME() provided by MySQL as defaults for date/time type columns.

Is there a way to use something like 'select currtime();' as the default for a column?

so my question is, how do I use ANYTHING other than one timestamp with the default value of now() for date/time defaults in MySQL.

About Dates And Times
I have form with drop down boxes, where folks enter dates and times, I have used implode and explode to format the dates, now I want to write those dates to mysql, I know mysql has its own date and time functions, is there a way to convert my php formatted dates and times into something that mysql will recognize and store as a time and date or does php have a function that will convert it and then allow it to be stored in date/time format in the database?

Number Of Times
I have a query that grabs 500 rows from 1 field within 1 table.

In this table. Each user will have 500 rows associated to them from within that table. So if there are 2 users then there will be 1000 rows. Each time 1 user executes the query, it grabs their 500 rows. If 2 users execute the query simultaneously, then this means that there are 1000 rows, all of which will be selected; 500 by one user and 500 by the other. If 3 users access the table simultaneously, then there are 1500 rows and so on. My question is how many users can execute this query simultaneously before we see performance loss?

Records Between Two Times
I have a table for a restaurant database with two fields open_time and close_time in TIME format. I want to be able to select all records that fall between open_time (when the restaurant opens) and close_time (when the restaurant closes).

I have this query:
Quote: Select hours_id from hours where curtime() between 'open_time' AND 'close_time';

It partially works, but its not working if the close_time is after midnight (i.e 1am = 01:00:00 in database). I can't figure out the issue with the difference between AM and PM.