MYSQL :: Quick Ones To Speed Up Access

Database Access Speed: More Tables Or More Fields In A Table?
I have a question: it is better to create a database where there are more tables or more fields in few tables? What I want is: high speed accessing, indexing and searching. So what do you suggest me?

Quick Join
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys.

Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id

How do I get the loser name?

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick Join
I have a quick SQL question:
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id
How do I get the loser name?

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

Quick COUNT()
The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

Quick Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value?

No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this.

UPDATE articles
SET published = ABS(published - 1)
WHERE id = x

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

Speed
I recently started using a MySql 5.0. I had been using a 4.1 server. After changing I've noticed a sufficient increase in the connection times. The biggest speed problem I've faced is when I try to read from a table. In 4.1 it seems rather instantaneous. In 5.0 I see a 2 to 3 second delay.

Speed Up
I have 3 tables
cat: The Main Category
scat: The Sub Category
product: The Product info, cat_ID and scat_ID

I need to output all product BY Main Category then sub category.

Right Now I get all the records for Main Category then
loop though and in the loop I get all Sub Categories Where cat_ID is the current Main Category.

Then I loop through again and get all product where the scat_ID id the current s_cat.

Then I loop through and output the product info.

Anyway the page load times are not great and I'm sure I am hitting the database way to much.

Speed
which is faster/better: Having a single table with all the information you need for a query in it but with duplicated data (not entire rows)
OR
Having what would be duplicated data in lookup tables.

This is in a database that will have millions of records to query. It needs to run super fast - like anything over 1s from submitting the form to getting the result is considered slow...My head is telling me to use lookup tables, because duplicated data is bad but surely thats going to have a performance hit with so many records and with high traffic - expected 25k+ visiters per day.

Can You See Any Way To Speed This Query Up A Bit?
SELECT SQL_CALC_FOUND_ROWS
title,
content,
URL,
id,
(MATCH(URL)
AGAINST ('$searchQuery' IN BOOLEAN MODE) * 1.5) +
(MATCH(title)
AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.8) +
(MATCH(content)
AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.5) AS score
FROM $searchType
WHERE
MATCH(title, URL, content)
AGAINST ('$searchQuery' IN BOOLEAN MODE)
AND content != ''
ORDER
BY score DESC
LIMIT $limit
OFFSET $offset

Speed Of Query
I was on the PHP forum and they sent me here. I am quite new to working with large databases (approx 1 million records per table) and am having problems with the speed of viewing the data. I have a page that requests all users from a certain department and displays their names etc (this uses a simple 'SELECT first_name etc FROM staff WHERE department = 3' style query).

There are around 800,000 staff in the table and so this query can take a long time to run (sometimes only to return 1 record). Is there any way of speeding up this query? Someone mentioned that creating an index on the department field may speed things up but they didn't seem sure, and I don't know how to do this so would only spend time learning if it might make a large difference! Large websites must deal with tables far larger than this and I was wondering how.

Speed This Query Up?
I have a simple query I am running, but it is taking 6 seconds to run. Im not sure what I can do to speed this up. Am I simply SOL or am I not doing something I could be?

Speed Up A Group By
would like to know whether CHAR(10) AUTO_INCREMENT will work like INT(10) AUTO_INCREMENT?

or is there another field definition that will allow me to implement alphabetical increment?

COUNT() Speed
I am fairly experienced with MySQL, but I am having trouble with this certain query. I am running mysql 4.1.18

The basic query is the following, I will refer to it from now on as '_BQ_' for simplicity:

Speed Issue
We had version 4.1 installed on a server and a multiuser application accessing it. Over a period of a year or so, accessing queries etc. has become slower and slower, although the number of records have increased dramatically over that time.

Recently we installed 4.1 again on a new server and moved the database over and instantly there was a dramatic increase in speed. However, over the last 3 or 4 weeks it has started slowing down again.

For the moment, let us assume that it is not the application that is causing the problem. Is there anything about the way MySQL is set up that might cause it to slow up in this way? We are using both InnoDB and ISAM tables.

DB Speed Issues?
I am having the oddest issue that I can't put my finger on. It's probably something simple..

I have two DB [prod & stage] on the same RedHat 9 server. These 2 databases share a copy of Apache 1.3.31 with same dbd/dbi drivers. This application is 100% perl with dbd/dbi.

Issue: When I point the code to the staging DB a particular page (Large calendar page, with lots of selects) takes 3 seconds to load. I then point the same code to the prod DB and the page takes 12 seconds to load.

What parameters could cause something this drastic? I will add one more thing here... the volume of data is the same (I copied the prod DB back to staging to rule that possibility out as well.)

Speed Of Listings
I am having problems with the speed of MY MYSQL database when delivring listings from a real estate agent management system developed using Perl and a MYSQL Database, we have tried Modperl but it mucks up too many other things and we have had to disable it.

When a user goes to a website such as www.propertypoint.com.au and performs a search for property listigns the results come up very quickly, but when a user clicks on a listing to view a full property page, it sometimes takes 10,15,20 and even 30 seconds for it to load.

MySQL Speed
I'm using a mySQL database to store all user information, as well as all the images (in blobs.) the site is plenty fast currently, but i'm worried that as it grows, all the database access will slow down a lot.

does anyone have any tips or information on speeding all of this up? would caching images on disk be faster than pulling them out of the database? i guess i'm really just asking for general mySQL optimization techniques. anyone?

Speed Issues
I have 15,000,000 bank records that are querried against 60,000 census records.

There is a primary index on the census table(concatonation of state/msa and tract) that corresponds to an index on the bank table(not unique on the bank table). I need these indexs to perform timely INNER JOIN querries.

NOTE - all querries that I describe are make temporary tables. They have to be for my app - unless there is an alternate way similar to make tables that I can use. Maybe w/ views?

The proscess works if I filter the bank table down to the state and county levels. If I just filter at the state level the querries drag - Illinois can be 5-10 minutes. I tried to add indexs on the STATE in both the bank and census table but it didn't seem to affect the peformance.

Is Mysql not made to handle 10s of millions of records? Is 10 minutes not long on this query? Am I properly indexing?

If I remove the census table from the equation and just do a straight WHERE things still slog at a slow pace

Speed From Second Machine Bad
I am new to MySQL (mysql-4.1.7-win) with MySql ODBC (MyODBC-3.51.10-x86-win-32bit) and (vb 6 with service pack 5 or Access 2003) to connect via adbc to MySQL.
My seccond machine are terribly slow and would like to know if there are any suggestions.

Speed To Lookup Value
I am considering having a database of 8,000 IP addresses of search engine robots and other bots. If I put this in a MySQL database, how long would it take to lookup that IP address in the database to check whether it is a surfer or bot on the site? Any ideas?

I would only be querying the IP address against the 8,000 values in the MySQL database.

Speed Up Insert
I have table in mysql database with hundreds of selects per second.Once in 15 minutes there is insert to this table.The problem is that this single insert take a lot of time about 1 sec.I tryied to play with mysql variables but without succses.
I did test and inserted to table with same structure without selects and it take 0.01 sec.

Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers.

SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;

Speed Of Join
I am having a heck of time trying to get this query to run decently fast. This query took 2 hours to run. Originally I was joining two tables with 200,000 and never stayed around long enough to see it finish. So, I wrote a script that dumped the results of one query into a holding table. That holding table has 3000 records. I can't figure out why this query is running so slow. Any ideas how to make it faster? If so, how much speed can I expect for an increase?

SELECT count(*) as bednum, needs_services.provide_start_date,needs_services.provide_end_date, shelter_bedlist_id, needs_services.inactive FROM holder LEFT JOIN needs_services ON needs_services.need_id = holder.uid AND needs_services.inactive != 1 GROUP BY needs_services.shelter_bedlist_id;

Retrieval Speed
I am in the phase of designing a large database which potentially could go up to 50,000,000 row. I wonder with that kind of number, how would the it affect the retrieval speed ? Let say I want to get 1 record out of this 50,000,000 I am holding in the mySQL table

Speed Up MySQL
I'm currently running mySQL 5.0 community server on Intel core 2 duo with 4GB and Vista business. I have two database, each of around 10million rows, 5GB in size. Whenever I perform operation on these, it takes a long time (>20min). Is there anyway to speed up the process?

Speed Up This Query
I have a table with just userid,indexid,and imgid and I use this:

SELECT count(userid), indexid, count( imgid )
FROM `photoindex`
GROUP BY `indexid`;

the table has 2 million rows and it says it take 2sec to finish but really, it takes 15 sec after sending the query

I think the lag time is due to mysql reading the entire table into a temporary table in order to do the GROUP BY.can anyone help me reduce this query time? is there a SQL setting to increase the memory to speed up this type of query?

The Problem Is About Access Denied For User...how Can I Access The Mysql??
you can find some problems below. the problem is about access denied for user...
how can I access the mysql? ......

Can Access Remotely, Cna't Access Locally (seems Backwards)
I have a database that I am trying to connect to a schema called lportal4_1_2 in mysql 4.1.13a (provided by my hosting company) runnning in fedora. I am trying to get a container to connect to it and was having problems. Since I didn't want to pollute the problem with the container, I wrote a small (hack) program that does the connection.

Here is the problem:
I can connect from my desktop (winxp) using a Java command line application, Mysql Administrator and mysql query browser and everything works fine. When I run the same Java command line app (using the exact same driver) I get the Access denied for user error that follows:
----
java.sql.SQLException: Access denied for user 'lportal'@'inkbrains.com' (using password: YES)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)...........................

MySQL Speed Test !!
let me explain, I have a Database with 30,000 rows having a single field of length 100 bytes My current database driver takes 0.339067955017 seconds to search through the entire database and find an entry at last record.

On What Depends Speed Of The Query?
I didnt have too much in common with large databases and i wonder what kind of query is suposed to be complicated. i mean in terms of performance. are there the golden rules like: when you have more then 5 where conditions use union or something?

do you think this query could be written in a "faster" way?

Query Vs Logic Speed
I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic?

What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need.

Would this speed things up? how much if any? and is it worth the effort?

How To Speed Up Select Query
I have a very big table >2GB. My query is as follows:

Select JDay, cda_lake.Cell_Num,NH4, NOx, TP, DO, pH from cda_lake, site_cells where Site_Num =1 and site_cells.Cell_Num = cda_lake.Cell_Num and truncate(JDay,0)=152 order by JDay,Cell_Num;

It took more 1 min to get 20504 records.

Can you tell me what's wrong?

Any Way To Speed Up Queries That Aggregate?
I am developing a web-based data analysis tool that will (hopefully) include large detailed record databases. I want to make the query response times as fast as possible, though I am not sure how fast that should be. Ultimately, everything will be done through PHP, though I am now testing straight from MySQL.

My first data set includes about 250,000 unique records, with about 120 fields (mostly shortint). Most of those fields are category type fields, that can be used in a GROUP BY statement. I have a few true numerical fields on which mathematical operations (mostly average) will be applied. The problem is that I want the users to be able to select any possible combination of Grouping variables, so there is no way I can index every possible combination. As a result, the queries are somewhat slow when I aggregate using GROUP BY with two categories - around 6 seconds. (Maybe this isn't slow?)

I've tried breaking up the table into smaller tables and joining, but that only makes things worse. The question is - is there anything I can do to set up the database, make sure I'm doing in the query, or change the MySQL settings to take advantage of my hardware? (Our Linux system has 7GB of RAM).

Speed Up BLOB Search
I have a database with about 60.000 rows that contain in total 500MB in one
TEXT colum.

Now I need do so some searches in the TEXT fields.
I use the following code:

SELECT * WHERE DATE_FORMAT(pubdate, '%Y%m%d')<='20041111' AND (tekst LIKE
'%america%') GROUP BY article_id ORDER BY DATE_FORMAT(pubdate,
'%Y%m%d%%H%i%S') DESC, bewerkt DESC

Is the (tekst LIKE '%america%') part the fastest way to search in a TEXT
colom? Is there a way to speed this up? For example with indexing?