SpirirHost Support Centre: MySQL Reference Manual p.173

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 3: Tutorial Introduction 173 OR field2_index = '1' The reason is that we haven't yet had time to come up with an ecient way to handle this in the general case. (The AND handling is, in comparison, now completely general and works very well.) For the moment you can solve this very eciently by using a TEMPORARY table. This type of optimisation is also very good if you are using very complicated queries where the SQL server does the optimisations in the wrong order. CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = SELECT * from tmp; DROP TABLE tmp; The above way to solve this query is in eect a UNION of two queries. See Section 6.4.1.2 [UNION], page 453. 3.5.8 Calculating Visits Per Day The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23); SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month; Which returns: +------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+ The above calculates how many dierent days was used for a given year/month combination, with automatic removal of duplicate entries. 3.5.9 Using AUTO_INCREMENT The AUTO_INCREMENT attribute can be used to generate an unique identity for new rows: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL,

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide