Working with large database tables

[Asheeown]

I'm working with calling records out of a database from two columns and the table is over 3 million records big and it takes a long time to result the query on a page.  I've heard of indexing but I can't find a good explanation or or tutorial on how to...any pointers?

[fenway]

Post your table structure (SHOW CREATE TABLE) your query, and the EXPLAIN output from your select.

[Asheeown]

PHP query
[code]$UserLogs = mysql_query("SELECT * FROM rated_cdrs WHERE Originating_TG IN ($Sources) AND UTCTime > '$StartDate' AND UTCTime < '$EndDate' ORDER BY '$Field' '$Order' LIMIT $from, $max_results") or die(mysql_error());[/code]

Table structure:
[quote]Field  Type  Null  Default
ID int(11) No   
LocalTime datetime Yes  NULL 
UTCTime datetime Yes  NULL 
ANI varchar(255) Yes  NULL 
Inpulsed_Digits varchar(255) Yes  NULL 
Outpulsed-Digits varchar(255) Yes  NULL 
Raw_Seconds int(11) Yes  NULL 
IB_Billed_Seconds int(11) Yes  NULL 
IB_Currency varchar(255) Yes  NULL 
Inbound_Revenue double Yes  NULL 
OB_Billed_Seconds int(11) Yes  NULL 
OB_Currency varchar(255) Yes  NULL 
Outbound_Cost varchar(255) Yes  NULL 
Originating_TG varchar(255) Yes  NULL 
Terminating_TG varchar(255) Yes  NULL 
Originating_Switch-specific Extra Field varchar(255) Yes  NULL 
Terminating_Switch-specific Extra Field varchar(255) Yes  NULL 
IB_Rated_Digits varchar(255) Yes  NULL 
OB_Rated_Digits varchar(255) Yes  NULL 
IB_Region_Name varchar(255) Yes  NULL 
OB_Region_Name varchar(255) Yes  NULL 


Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 3008302  ID
Originating_TG INDEX 18  Originating_TG
UTCTime INDEX 3008302  UTCTime[/quote]



Now the query works fine it does it's job but takes almost 15 to 20 seconds to accomblish

[fenway]

OK, now post the interpolated query... if your'e taking an arbitrary field, you're going to have indexing issue for sure.

[Asheeown]

What exactly do you mean? by interpolated query

[effigy]

The SQL that you posted uses variables that PHP substitutes since they're inside of double quotes (interpolation). What does the actual SQL look like?

[Asheeown]

[code]SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime > '2006-02-22 01:00:00' AND UTCTime < '2007-02-22 01:00:00' ORDER BY Originating_TG DESC LIMIT 0, 30[/code]

Is that what your looking for?

[Asheeown]

Hello?

[effigy]

[quote author=Fearsoldier link=topic=124202.msg514344#msg514344 date=1169847007]
Hello?
[/quote]

Hi. This isn't a chat room. People come and go, and are often occupied with other things.

Yes. That's the SQL fenway was looking for. He also asked for an EXPLAIN, which I haven't seen yet. Run the same SQL that you posted, except add "EXPLAIN " to the beginning.

[Asheeown]

I added EXPLAIN to my query (The start of the query) and nothing shows I echoed out the results, the query, no results from the explain

[Asheeown]

And I am sorry for the impatient behavior, runs in the family if i do it again please yell at me by all means

[effigy]

If you're not in the mysql client, try this through your web interface:

[code]
$q = mysqli_query($db, 'explain select...');
while ($r = mysqli_fetch_array($q, MYSQLI_ASSOC)) {
print_r($r);
}
[/code]

Use mysql functions if the mysqli's do not work for you.

[Asheeown]

[quote]SQL query: EXPLAIN SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime > '2006-02-22 01:00:00' AND UTCTime < '2007-02-22 01:00:00' ORDER BY Originating_TG DESC LIMIT 0, 30;
Rows: 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE rated_cdrs range Originating_TG,UTCTime UTCTime 9 NULL 601230 Using where; Using filesort[/quote]


went through PHPmyadmin

[Asheeown]

I have the two rows I need indexed but how exactly do I use that index?

[effigy]

I'm new to EXPLAIN and optimization myself; here's my attempt:

Why is UTCTime an index? I wonder if BETWEEN would be any better than ">" and "<", or if it's only cleaner looking? You may also want to try forcing the use of the "Originating_TG" key: "SELECT * FROM rated_cdrs FORCE INDEX Originating_TG..."--I'm not sure if you can use this without a JOIN though.

[Asheeown]

the between function does sound like a much cleaner way to check for that row, so how exactly would i go about doing a join function

[effigy]

See if BETWEEN speeds it up any. Afterwards, try the FORCE INDEX. If it gives you an error, then scratch the idea. Perhaps you could create an index that uses the Originating and Time columns.

[Asheeown]

[quote]SELECT * FROM rated_cdrs FORCE INDEX IdTime WHERE Originating_TG IN ($Sources) AND UTCTime BETWEEN '$StartDate' AND '$EndDate' ORDER BY '$Field' '$Order' LIMIT $from, $max_results[/quote]

The between function was a great idea and the index: IdTime is the two columns together in one index but it still takes about 10 seconds for the query to finish

[shoz]

[quote author=Fearsoldier link=topic=124202.msg514476#msg514476 date=1169857408]
[quote]SELECT * FROM rated_cdrs FORCE INDEX IdTime WHERE Originating_TG IN ($Sources) AND UTCTime BETWEEN '$StartDate' AND '$EndDate' ORDER BY '$Field' '$Order' LIMIT $from, $max_results[/quote]
The between function was a great idea and the index: IdTime is the two columns together in one index but it still takes about 10 seconds for the query to finish
[/quote]

The index on (Originating_TG, UTCTime) should have helped. Are you certain that there's a multi-column index on both columns? If so, remember to use FORCE INDEX as has been suggested. The syntax should be "FORCE INDEX(columnname)" however. Also note that the sort field matters. Originating_TG should be the ORDER BY field.

If the index does not exist create it (keep in mind that the order of the columns in the index matters) and run the query again. Remember to use FORCE INDEX(indexname) if MYSQL does not use the new index.

If there's still a problem post the version of mysql you're using "SELECT VERSION()", the new EXPLAIN for the query (with and without FORCE INDEX) and the result of "SHOW INDEX FROM tablename".

[Asheeown]

I dont even know where to continue with the index after everything I tried my pages are taking longer as I need to pull more information out of the database and it's getting up to about 2-3 minutes a page

[shoz]

[quote author=Fearsoldier link=topic=124202.msg514628#msg514628 date=1169876551]
I dont even know where to continue with the index after everything I tried my pages are taking longer as I need to pull more information out of the database and it's getting up to about 2-3 minutes a page
[/quote]

Elaborate on what you mean by "more information", perhaps a solution can be provided. Currently we are only attempting to optimize the query you posted. You should be testing it on its own to ensure that the results aren't affected by other portions of your script.

If you do decide to continue, post the information requested in my last post.

[Hypnos]

PHPMyAdmin will tell the results of an EXPLAIN. It will just be a one row result.

Paste your query in to PHPMyAdmin in "SQL", run it, then click "Explain SQL" at the top. It will tell you if it used your indexes or not for that query.

On a table that big, every one of your page queries should be using indexes.

If you read this, you might get a better understanding of why we are telling you to use indexes, and what they are:
http://www.databasejournal.com/features/mysql/article.php/1382791


Don't use SELECT * if you don't need every column. Just select the columns you need.

If this is with PHP, use mysql_fetch_assoc or mysql_fetch_row (depending on what you want your keys as), instead of mysql_fetch_array.

[Asheeown]

Two columns:  "Originating_TG" and "UTCTime" they are in a tied index for a reason, because they are my main means of searching

What I want to know is exactly why it's ignoring the INDEX and taking longer than ever to go through

[Asheeown]

It doesn't make sense it completely ignores the index, is it the date? Should I have two separate queries with one column in each? I think that would go slower

[shoz]

[quote=shoz]
If you do decide to continue, post the information requested in my last post.
[/quote]