Asheeown Posted January 26, 2007 Share Posted January 26, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/ Share on other sites More sharing options...
fenway Posted January 26, 2007 Share Posted January 26, 2007 Post your table structure (SHOW CREATE TABLE) your query, and the EXPLAIN output from your select. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170039 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 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 DefaultID 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 FieldPRIMARY PRIMARY 3008302 IDOriginating_TG INDEX 18 Originating_TGUTCTime INDEX 3008302 UTCTime[/quote]Now the query works fine it does it's job but takes almost 15 to 20 seconds to accomblish Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170048 Share on other sites More sharing options...
fenway Posted January 26, 2007 Share Posted January 26, 2007 OK, now post the interpolated query... if your'e taking an arbitrary field, you're going to have indexing issue for sure. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170054 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 What exactly do you mean? by interpolated query Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170061 Share on other sites More sharing options...
effigy Posted January 26, 2007 Share Posted January 26, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170073 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 [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? Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170077 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 Hello? Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170102 Share on other sites More sharing options...
effigy Posted January 26, 2007 Share Posted January 26, 2007 [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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170122 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170128 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 And I am sorry for the impatient behavior, runs in the family if i do it again please yell at me by all means Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170129 Share on other sites More sharing options...
effigy Posted January 26, 2007 Share Posted January 26, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170136 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 [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: 1id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE rated_cdrs range Originating_TG,UTCTime UTCTime 9 NULL 601230 Using where; Using filesort[/quote]went through PHPmyadmin Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170139 Share on other sites More sharing options...
Asheeown Posted January 26, 2007 Author Share Posted January 26, 2007 I have the two rows I need indexed but how exactly do I use that index? Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170193 Share on other sites More sharing options...
effigy Posted January 26, 2007 Share Posted January 26, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170214 Share on other sites More sharing options...
Asheeown Posted January 27, 2007 Author Share Posted January 27, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170217 Share on other sites More sharing options...
effigy Posted January 27, 2007 Share Posted January 27, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170225 Share on other sites More sharing options...
Asheeown Posted January 27, 2007 Author Share Posted January 27, 2007 [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 Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170231 Share on other sites More sharing options...
shoz Posted January 27, 2007 Share Posted January 27, 2007 [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". Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170336 Share on other sites More sharing options...
Asheeown Posted January 27, 2007 Author Share Posted January 27, 2007 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 Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170383 Share on other sites More sharing options...
shoz Posted January 27, 2007 Share Posted January 27, 2007 [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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170457 Share on other sites More sharing options...
Hypnos Posted January 27, 2007 Share Posted January 27, 2007 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/1382791Don'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. Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170520 Share on other sites More sharing options...
Asheeown Posted January 27, 2007 Author Share Posted January 27, 2007 Two columns: "Originating_TG" and "UTCTime" they are in a tied index for a reason, because they are my main means of searchingWhat I want to know is exactly why it's ignoring the INDEX and taking longer than ever to go through Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-170732 Share on other sites More sharing options...
Asheeown Posted January 28, 2007 Author Share Posted January 28, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-171232 Share on other sites More sharing options...
shoz Posted January 28, 2007 Share Posted January 28, 2007 [quote=shoz]If you do decide to continue, post the information requested in my last post.[/quote] Quote Link to comment https://forums.phpfreaks.com/topic/35867-working-with-large-database-tables/#findComment-171249 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.