MYSQL :: Indexes With Multiple Fields

Multiple Indexes In One Query
I do a query like this on a couple million rows and it's too slow.

SELECT `title`,`link`,`description` FROM `files` WHERE `spam` =0 AND `porn` = 0 AND `lastvisit` > 0 AND MATCH (`host`,`path`,`title`,`description`) AGAINST ('some query')

I know it's using my FULLTEXT index on host, path, etc.

I have another INDEX index on spam,porn, and lastvisit as well. Is it using this index already? If not is there a way to get it to use both indexes together?

Multiple Identical Indexes
I don't know if this belongs here but I wasn't sure where to post.
We have a database the size of 400mb (mysql 4.1.16, innodb). We were having some performance issues (deadlocks thrashing the server) so we started closely examining the database structure. We noticd that there are multiple indexes for the same table on the same column for many of the tables. I'm guessing this can't help in terms of performance and resource overhead. What type of perfromance remifications will multiple, identical indexes have?

Multiple Indexes In 1 Table ( Novice )
im rationed on table space and would like to create a no. of diffrent indexes in 1 table called photograph, so i can have a table full of diffrent galleries.

example

gallery1
- photo_id
- image
- caption
- viewed

gallery2
- photo_id
- image
- caption
- viewed

and so on

so when i call gallery 1, it will show the photos from that index, is that correct? if so how would i struture this?

still learning here, if this makes any sense..

any help would be great,

Multiple Fields
i have a query regarding mysql like clause.can we use
select name like 'john%' or 'bill%' from nametable ;
Is the above syntax correct or is it wrong.If wrong please give me the correct syntax.
what i am trying to do is that i am trying to select either john or bill with sum last name in all the rows.

Search With Multiple Fields
I have a form with essentially 5 different selects, let's call them country, state, subject area, tag, and age range. What I want to do is find all of the users who are the closest match to the search query (once I know how to do this, I can modify the query to find their names, email addresses etc...)

In the database, users have a country_id (not name!), state_id in one table, and subject area id, tag id, and age range id in another table (all in the same column as multiple rows for the same user).

I want to do a database search and match all of the users who meet at least 1 of the criteria, and sort them in order of closest match (all 5 criterion) to least match. Assuming I consider the order of preference to be subject area, tag, age range, country, and then state, how do I do this?

To make the problem even slightly more difficult, the form doesn't return the actual country name, it returns the row the country shows up in the country table (same for the other fields) so I'm currently doing a look up for each field to figure out which country/state/etc... I'm actually dealing with.

I don't see how I can do a FULLTEXT search for this information because it is all in different columns.

Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this:

UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345

UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432

How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.

Count Of Multiple Fields
I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient).

I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value.

# Example
UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96;
# Up to 200 fields

Or am I stuck with this query? (which works).

Multiple Distinct Fields
Okay SQL gurus, I have a table laid out as so:
RowID | UserID | UserType | pID

Now, how can I write a SQL query that would select every distinct UserID and pID combination.

So if the data in the table were:

1 jjdoe internal 19
2 jjdoe internal 6
3 jjdoe internal 6

It would only return rows 1 and 2.

Ordering Multiple Fields
I'm relatively new to mySQL so I've been looking around the internet for help on how to order based on a user-defined pattern and haven't seen exactly what I'm looking for. If someone could help with this example I created to illustrate what I'm trying to do in general, I'd greatly appreciate it.Let's say I have a table in my database of Army officers with the following fields: rank, lastName, firstName.

I'm looking for the right query so I can display in an HTML table every officer in the mySQL table and order the rows first by rank, then by lastName (if ranks are the same), then by firstName (if ranks and last names are the same).

Displaying Multiple Fields
I am sure amongst this community that this question is rather straight forward.
I have a database where a table is added that contains the user login, password and other user information.
What I wish to do now is for each user I want to be able to add a product description, old price and new price.
So when this particular user logs in, they will see a list of products on one column and then the old price in another column and new price in the third column.
The prices obviously have to be matched to the same product.
Eg:
productA new_price1 old_price2
productB new_priceX old_priceY

How would one do this?

Comparing Multiple ID Fields
I have an advert table which contains three category ID fields (catid, catid2, catid3). These catid fields match up with the catid field in the category table. I want to run a query that returns any row that contains any mention of a specific category.

SELECT * FROM advert, category
WHERE advert.catid = category.catid
AND advert.catid = '3'

The above query works fine for the first advert.catid field, but once I introduce...:

OR advert.catid2 = '3'

... the results start getting strange. The same row is returned 9 times.

GROUP BY Multiple Fields
Can somebody show me how to properly use GROUP BY if I'm using multiple fields.
For instance:

I have a table that has a month field and a year field. I want to group all entries by month and year. How do I prevent grouping a record from January 2007 with a record from January 2008?

Duplicates On Multiple Fields
The table has fields:
home_id, owner, street_number, direction_one, street, street_type, city

I have a problem where the same address information was entered in but with different owners. How do I find all the duplicates? I tried this query:
SELECT home_id, street_number, street, city
FROM home_data
GROUP BY street
HAVING ( COUNT(street) > 1 )

but that only works for duplicates on street, at the very least it should check for dups on street_number and street and city. When I ran the above query it took forever. My table has over a million records in it. Basically my computer froze up but kept running and I kept getting virtual memory errors.

How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value.

I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

How Change Multiple Mysql Fields?
I have a table with 2,733 records.

I need to change a field (published) on all records to show ....

Multiple Search Fields And Subqueries?
I'm trying to construct a query in PHP that will be able to handle multiple search fields. The data is in multiple tables.

COMPANY
company_id
name
address

SERVICE TYPE
company_id
service

OFFICE LOCATIONS
company_id
city_name

My question is how do I construct a query that will allow there to be these three search fields (company name, service type, location).

I know that subqueries are probably needed, but I'm a newbie and still not quite clear how to get these to work properly.

The queries I've been able to construct return zero results unless all fields are filled in, or they return multiple listings of the company name since the company may have more than one office location. I don't want the user to be required to fill in all fields to get meaningful results.

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Sort By Multiple Fields In Different Directions?
I'm wondering if I can sort by multiple categories in different directions. I tried this and it doesn't work:

SELECT * FROM table ORDER BY field1 DESC ORDER BY field2 ASC LIMIT 0,10

JOINing From Multiple Fields To One Table
I have two tables in a MySQL database. One has the publication data (title, ISBN etc), and the other has the data on the authors that wrote it (first name, second name, etc.)

I am trying to produce a single recordset that will show the authors' names (first name + a space + second name). The staff.staffID corresponds to publications.author1, publications.author2, etc.

The problem I have is that there are potentially 5 authors for each publication record, so when I try to LEFT JOIN them all them to the staff table, I get a '1066 not unique table / alias: staff' error - I can see why this is happening (becuase of the 5 left joins onto the same table) but how should I change the code to get the results that I need? Code:

Multiple Fields Linking To One Foreign Key
I have a table that is going to track project info, time, date, people working on the project, materials, etc.

My question is what would be the best way to have multiple fields for the people working on the project, for example this is my current layout (don't laugh too hard):

tbl_washLog (washID*, time, date, crewID**, crewID2**, crewID3**, tailID, paintID)

The crew ID fields need to be foreign keys back to the crewID field in the crew table which is:

tbl_crew (crewID*, firstName, lastName)

(* = Primary Key, ** = Foreign Key)

Is it possible to have crewID, crewID2 and crewID3 all be foreign keys of tbl_crew.crewID? If not, what would be the best way to go about doing this?

Select Fields Accros Multiple Dbs
I am trying to use the SELECT DISTINCT statement to create one list of email address I have which are in two different databases, I have tried the below syntax, but does not seem to work...can anyone please tell me where I am going wrong!

SELECT DISTINCT m2e_db.email, events_bookings.email FROM m2e_db, events_bookings ----Ment2Excel (M2E)----
--LEARN BY EXAMPLE--

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

Extract Date Into Multiple Form Fields
I'm storing standard datetime values in a MySQL table (i.e. 1999-12-31 23:59:59). A query such as:

"SELECT thedate FROM tablename"

returns the expected 1999-12-31 23:59:59

How can I extract the indivdual elements into separate PHP variables like:

theyear = 1999
themonth = 12
theday = 31
thehour = 23
theminute = 59
thesecond = 59

I've searched through the forums and thought DATE_FORMAT would be the ticket, but so far haven't been able to find anything that addresses this particular issue.

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Is It Possible To Sum The Values Of Multiple Fields On A Single SQL Record?
I have fields for Goals and Assists.

I want to take those 2 fields and sum them to make a 3rd field PTS, then send them to variable in my PHP code to be printed to the screen.

My statement is this:

SELECT player_stats.G, player_stats.A, SUM (player_stats.G + player_stats.A) PTS
FROM player_stats
WHERE player_stats.PID =10
AND player_stats.season =1

The statement breaks with the bolded part.

Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name':

tbl_names:
idname
------
1Bob
2Jeff
3Fred
4Joe
5Bill

I then have another table which contains several fields which hold id's
from the above table:

tbl_output:
idperson1person2person3
-----------------------
1231
2543

I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:

SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.person
WHERE tbl_output.id = ?

but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:

person1person2person3
---------------------
JeffFredBob

Sorting By Same-data Fields From Multiple Tables *without* UNION
Is there anyway I can sort by two columns (which are the same datatype) from separate tables (whether selected via Table1, Table2... or by LEFT JOIN) without using UNION?

Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation:

SELECT
user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province
FROM
user, languagefield, city, province
WHERE
user.id = %i
AND languagefield.language = %i
AND city.id = user.city
#tricky part
AND city.name = languagefield.id
AND city.province = province.id
AND province.name = languagefield.id
So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses?

Im using this db tbl scheme:

table : city
id (int4) primary key
name (int6) ref languagefield.id
province (int4) ref province.id

table : province
id (int4) primary key
name (int6) ref languagefield.id

table : user
id (int4) primary key
firstname (varchar32)
lastname (varchar32)
city (int4) ref city.id

table : language
id (int3) primary key
char2name (varchar2)
name (int6) ref languagefield.id

table : languagefield
id (int6)
language (int3) ref language.id
value (varchar255)

id + language unique

*****

I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Delimited Fields Or Unused Fields
I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often.  So 4 might well be empty.  Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields?  (the same issue there really)

The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better.  I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better.  I'm thinking efficiency, speed, db load, and so on.

Use Of INDEXES
I have a tiny question about indexes... Why we use indexes,what is the purpose of indexes, what are the benefits of indexes...

What Are Indexes?
What exactly are indexes?

are they like Foriegn Keys?

How do you make Foriegn Keys?

How would you go about specifying the primary key link to the foriegn key?

whats a good situation when indexes are most needed?

Use Indexes?
My table has some indexes declared. How I can use them in the select queries in order to achieve better performance?

Indexes
I have a table which has a range of values all with unique id's. The primary key is an auto-increment field.

Imagine: 5 records (1000, 1001, 1002, 1003, 1004)

I have a query:

SELECT * FROM mytable WHERE id IN (1002,1004,1001,1000, 1003)

I want the order of the returned records to be in order in which the id's are specified using the IN keyword.

The result is always returned in primary key order and not the order I specify with IN.

Indexes
I want to create a index to help the following statement run faster, can anyone suggest which would be the best index to create:

select * from profile, album, movie, book, contact
where profile.album = album.albumid
and profile.movie = movie.movieid
and profile.book = book.bookid
and profile.username=contact.username
and profile.sex != "male"
and profile.searchsex != "female"
and profile.relationship != "dating"
and photo_validated != "yes";

Also is there a way to see which indexes yo have already created ie. is there a "show indexes" or "desc indexes" statement that will show me all the indexes on a database or table.

Use Of Indexes
I read up about using 'index' but am not clear about it at all. Is there some link somewhere that explains this with an example so I can see how its useful?

Best Use Of Indexes
I have a situation where I am doing the following:

Example keys:

key1
key2
key3

Now "key1" is used a lot in WHERE queries with "key2" & "key3"; however they are not used all together at once.. "key1" is only used with 2 & 3 seperately.

So I was wondering is it best to create 2 seperate indexes such as:

key1
key2

key1
key3

..or create one such as..

key1
key2
key3

?

I created the two seperate ones, but phpmyadmin is complaining about me having two indexes created for "key1".

Indexes
I've been over the documentation and am still a bit confused on the most beneficial way to set up indexes in MySQL for certain tables. Imagine I have a table that is used purely for cross-reference, containing 4 columns. A query into this table may involve joining or filtering on anywhere from one to all of these columns. Is it best to:

A. set up one index for each possible combination/arrangement of the columns,
B. set up four indexes, with one column in each, or
C. don't index this table because it's not appropriate.

Indexes
i have a table with about 1.5 million records - it has about 80 columns

up until this point i have always had many indexes and i have to say it is very fast

anyway purely in a development stage i had to create a duplicate of the table and add some more columns, no indexes are defined on the new table.

it works as fast if not faster without any indexes!

am i missing something here?

Two Indexes
I have a DB made up o three tables.
I created a multi-column index called idx on (table1.id, table2.id).
I also would like to create an index idtot on (table1.id,table2.id, table3.id)
and an index idp on (table1.id, table2.id).
First of all I would like to know if this is possible.
If so,
when I make a query like

"select table1.id from table1,table3 where table1.id=table3.id",
MySQL automatically chooses the right index idp to make the search, or
do I have to specify it? (and if so, how?).

Indexes
i have a table (all_data) which has 750,000 rows.
it has a primary key (unique_ref) which is fine.
there is another column (prev_unique_ref) which i would like to add a unique reference to but it is not processing.
there are some null values in the column and the rest are all completely different but documentation i am reading does not to seem to suggest that should be a problem.

Indexes
I'm wading through the waters of "MySQL/PHP Database Applications," and I still cannot get this INDEX malarcky into my head. What exactly is an index? What's a working example that would put it into clear understanding

Indexes
Do you all think I should add indexes to the sortable fields I have available on my site.

Check the links on the upper left corner where it says "sort by".
http://gifdump.com/browse/all/

Indexes
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements.
So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns.
Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report.
given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?

Indexes With Multiple Fields

I have seen some tables that have indexes which contain multiple table fields. What is the purpose of having an index with multiple fields instead of a seperate index for each?