Re: how to speed up a simple query? need some help here...
> Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index: > http://dev.mysql.com/doc/mysql/en/EXPLAIN.html This partially solved my problem. Thanks a lot. However I am facing a new problem here. The query where I am ordering by a column is much more slowly than the same query NOT using SQL_CALC_FOUND_ROWS select SQL_CALC_FOUND_ROWS * from table by date desc - this query takes about 1.2 s select * from table by date desc - this query takes about 0.1 s Using explain I have noticed that the first query is using filesort and I cannot figure out why exactly... I could use count() to figure out the total number of results for the navigation and this would be defintelly much faster since now as I have tested but I was thinking if anyone else had this problem before and if you have found any work around... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problem - need some help here or is it just impossible the way i want it to work?
Hi there,
I've been trying around for some time now and i just don't see a solution to
my problem:
I have a table called "feiertage" ("Holidays"):;
mysql> select * from feiertage;explain feiertage;
++
| datum |
++
| 2001-12-24 |
| 2001-12-25 |
| 2001-12-26 |
| 2002-01-01 |
++
4 rows in set (0.81 sec)
+---+--+--+-++---+
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-++---+
| datum | date | | PRI | -00-00 | |
+---+--+--+-++---+
1 row in set (0.81 sec)
now i want to know the last date, that is not listed in that table and that
is not on a weekend and is earlier or the same than a given date:
assume 2001-12-29 is the given date, then i tried around with a loop in perl
increasing the INTERVAL.
select DATE_SUB('2001-12-29', INTERVAL 0 DAY) as tag from feiertage where
datum = DATE_SUB('2001-12-29', INTERVAL 0 DAY) or
WEEKDAY(DATE_SUB('2001-12-29', INTERVAL 0 DAY))>4;
tells me that this date is on a weekend or a holiday. that doesn't rellay
help me, what i need is a query that gives me "2001-12-28" (which is the
friday). i played around for 2 hours now but have no idea how to make it work with
just one query. I could think of ways to do it with perl and loops and stuff
like that, increasing the INTERVAL until i get zero rows which would mean i
stepped backwards enough days, but then i would need another query to
actually get the date in the right form "2001-12-28" which i need. But i have still
hope there is a way without the overhead of loops and stuff...
has anybody some suggestion?
thanks a lot for any hints
Richard
--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need some help here...
Just my opinion, but I prefer numeric keys that the users *can't* see, to alpha keys that the users can see: 1. numeric keys are faster 2. using numeric keys that the user can't see allows you to open up(for UPDATE) ALL the fields that the user can see including a field like School Name. Problem with alpha School Name: If you allow the users to change the school name, you'd have to change *ALL* occurrences of the old school name to the new school name - in all tables/rows that reference the old school name - could be a big programming effort. If you don't allow the users to change the school name, they might be unhappy - or maybe you allow users to submit a form saying they want to change school name from a to b, and you write a program which goes through the database changing every occurrence of a to b. Again, a lot of work, and users who are probably unhappy. Solution: Instead of having the School Name be your primary key, have a field called school_id that the users can't see or update - once assigned to a school, that school_id is the primary key to that school's info forever. If another table/record needs to refer to a particular school, you store the school_id in that table/record, instead of storing the school name. Since the users can never update the school_id, you don't have to worry about writing extra code to update all the occurrences of a to b - you can then allow users to update the school name field without affecting the key - the school name is just another data field. This may not be the best example for me to demonstrate why I think numeric keys are better - maybe in this case the School Name never changes for any of the schools. But if it could change, then numeric keys could be the answer. MySQL auto-increment fields are great for this, and Oracle has a similar type of field called a "sequence". Where it makes sense, my preference is to use an auto-increment INTEGER field as the primary key to a table. Keeping the numeric keys used for basic retrieval separate from the user viewable/updatable fields makes for a smaller, less-complex, and more flexible program. Again, just my opinion. HTH. -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com John Jensen [[EMAIL PROTECTED]] wrote: > Think of the tables in your database as linked sets. > > I have a college database where school description as info associated > in three other tables, allowing one school description, one or more > contacts, requirements for one or more programs, and each program has > one or more degrees associated, in the last table. > > In each table, the School name is replicated, serving as the primary > key in only the first. The primary key of the second is replicated in > the third, and so on. Only degrees have a number as the key, with > School name and the key of the previous table entered as fields, to > link everything together. > > How you use tables will be defined by how your data needs to be > organized. Understanding your data needs is your first step. > > On 5 Feb 2001, at 10:55, Chris Toth wrote: > > > > > > > I'm having a extremely hard time grasping the concept of multiple > > tables. So far, I've been using just one table when designing a > > database. But now I have to design a database for a trouble-ticket > > system for our department. I've written out the design of the tables, > > but the part I don't understand is how the tables relate to each > > other. Do I need to use foreign keys? But if I do, I thought MySQL > > didn't support foreign keys? > > > > BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find > > my answers. > > > > Just in case it matters, I'm going to have one table full of faculty > > info, one of staff info, one for the trouble ticket itself, and > > possibly one for actions performed on the trouble ticket. > > > > > John Jensen > 520 Goshawk Court > Bakersfield, CA 93309 > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need some help here...
Think of the tables in your database as linked sets. I have a college database where school description as info associated in three other tables, allowing one school description, one or more contacts, requirements for one or more programs, and each program has one or more degrees associated, in the last table. In each table, the School name is replicated, serving as the primary key in only the first. The primary key of the second is replicated in the third, and so on. Only degrees have a number as the key, with School name and the key of the previous table entered as fields, to link everything together. How you use tables will be defined by how your data needs to be organized. Understanding your data needs is your first step. On 5 Feb 2001, at 10:55, Chris Toth wrote: > > > I'm having a extremely hard time grasping the concept of multiple > tables. So far, I've been using just one table when designing a > database. But now I have to design a database for a trouble-ticket > system for our department. I've written out the design of the tables, > but the part I don't understand is how the tables relate to each > other. Do I need to use foreign keys? But if I do, I thought MySQL > didn't support foreign keys? > > BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find > my answers. > > Just in case it matters, I'm going to have one table full of faculty > info, one of staff info, one for the trouble ticket itself, and > possibly one for actions performed on the trouble ticket. > John Jensen 520 Goshawk Court Bakersfield, CA 93309 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need some help here...
Hello, I tend to do either what Hardy suggested depending on the needs of your database. If the trouble ticket table is to be associated with both faculty and staff, and that fac/staff can have more than one trouble ticket I like to create a relational table. To do this you create a separate table with to fields, one for the id for the faculty-table and the other with the id for the trouble ticket-table (and then one for staff..) Then to see if a faculty member has a trouble ticket, just select from the relational-fac table 'where faculty-id = 'id''. I can explain further if I didn't quite explain myself good enough :) Go with what you prefer and stick with it :) Atle On Mon, 5 Feb 2001, Chris Toth wrote: > > > I'm having a extremely hard time grasping the concept of multiple tables. So > far, I've been using just one table when designing a database. But now I > have to design a database for a trouble-ticket system for our department. > I've written out the design of the tables, but the part I don't understand > is how the tables relate to each other. Do I need to use foreign keys? But > if I do, I thought MySQL didn't support foreign keys? > > BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find my > answers. > > Just in case it matters, I'm going to have one table full of faculty info, > one of staff info, one for the trouble ticket itself, and possibly one for > actions performed on the trouble ticket. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need some help here...
Try going to www.mysql.com and search for "foreign key" in the search box - I found 28 matches - here are the 1st 3 matches: 1. MySQL Manual | 5.4.5 Foreign Keys [4] 2. MySQL Manual | 5.4.5.1 Reasons NOT to Use Foreign Keys constraints [4] 3. MySQL Manual | 9.3.6 Using Foreign Keys [4] That may be enough to get you going. You definitely don't "need" to use foreign keys - and you really can't use them with MySQL since they are not "really" supported. Faculty Staff Trouble Trouble_actions I prefer to give each table a numeric "key" - you'll have to study the MySQL data types, but I like to use Integer "auto_increment" fields for that. So, Faculty - primary key faculty_id (auto_increment) Staff - primary key staff_id (auto_increment) Trouble - primary key trouble_id (auto_increment) - columns for Trouble table will probably include * faculty_id - if faculty could be involved with Trouble ticket * staff_id - if staff could be involved with Trouble ticket Trouble_actions - primary key trouble_action_id (auto_increment) - one of the columns *must* be "trouble_id", so that each trouble_action can be related to its parent Trouble ticket. - columns for Trouble table may include * faculty_id - if faculty could be involved with Trouble ticket action * staff_id - if staff could be involved with Trouble ticket action Each table should include columns for the primary(?) key of the other tables that it needs to relate to. An easy example is the Company/Employee relationship - one table for Company, and one table for Employee - the Employee table needs to have a way to relate to the Company that the Employee works for, so the Employee table would contain company_id, assuming company_id is a key(probably primary key) in the Company table. This gets you 2 things: 1. If you have a company, and want to find all employees for that company, you can SELECT all the employees with that company_id, and 2. If you have an employee, and want to know what company that employee works for, you can use the company_id stored in the Employee table to find the Company. Pretty simple example, but hopefully it helps. -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com Chris Toth [[EMAIL PROTECTED]] wrote: > > > I'm having a extremely hard time grasping the concept of multiple tables. So > far, I've been using just one table when designing a database. But now I > have to design a database for a trouble-ticket system for our department. > I've written out the design of the tables, but the part I don't understand > is how the tables relate to each other. Do I need to use foreign keys? But > if I do, I thought MySQL didn't support foreign keys? > > BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find my > answers. > > Just in case it matters, I'm going to have one table full of faculty info, > one of staff info, one for the trouble ticket itself, and possibly one for > actions performed on the trouble ticket. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: need some help here...
MySQl does not support FK relationship constraints. meaning that if you put a value in a FK, it won't automatically check to make sure it's a valid value in the related table. You can, however use FK's. That is how you should related your tables. It is up to you, the developer, to make sure that no bad data gets put in them though. Cal http://www.calevans.com -Original Message- From: Chris Toth [mailto:[EMAIL PROTECTED]] Sent: Monday, February 05, 2001 9:56 AM To: MySQL List Subject: need some help here... I'm having a extremely hard time grasping the concept of multiple tables. So far, I've been using just one table when designing a database. But now I have to design a database for a trouble-ticket system for our department. I've written out the design of the tables, but the part I don't understand is how the tables relate to each other. Do I need to use foreign keys? But if I do, I thought MySQL didn't support foreign keys? BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find my answers. Just in case it matters, I'm going to have one table full of faculty info, one of staff info, one for the trouble ticket itself, and possibly one for actions performed on the trouble ticket. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
need some help here...
I'm having a extremely hard time grasping the concept of multiple tables. So far, I've been using just one table when designing a database. But now I have to design a database for a trouble-ticket system for our department. I've written out the design of the tables, but the part I don't understand is how the tables relate to each other. Do I need to use foreign keys? But if I do, I thought MySQL didn't support foreign keys? BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find my answers. Just in case it matters, I'm going to have one table full of faculty info, one of staff info, one for the trouble ticket itself, and possibly one for actions performed on the trouble ticket.
